I ran a query to find 1)the current distribution group and corresponding account id number and 2) the historical distribution group and corresponding account id number from checks written in contract year 2008.

Here is the sql statement from my query:

SELECT EMPLOYEE_MASTER.EMPLOYEE_NO, EMPLOYEE_DISTRIBUTIONS.DISTRIBUTION_GROUP, EMPLOYEE_DISTRIBUTIONS.ACCOUNT_ID, PAYS_HISTORY.DISTRIBUTION_GROUP, ACCOUNT_HISTORY.ACCOUNT_ID, ACCOUNT_HISTORY.CHECK_KEY
FROM HRS.ACCOUNT_HISTORY ACCOUNT_HISTORY, HRS.EMPLOYEE_DISTRIBUTIONS EMPLOYEE_DISTRIBUTIONS, HRS.EMPLOYEE_MASTER EMPLOYEE_MASTER, HRS.PAYS_HISTORY PAYS_HISTORY
WHERE EMPLOYEE_DISTRIBUTIONS.EMPLOYEE_NO = EMPLOYEE_MASTER.EMPLOYEE_NO AND PAYS_HISTORY.EMPLOYEE_NO = EMPLOYEE_MASTER.EMPLOYEE_NO AND ACCOUNT_HISTORY.CHECK_KEY = PAYS_HISTORY.CHECK_KEY AND ((PAYS_HISTORY.CONTRACT_YEAR=2008) AND (ACCOUNT_HISTORY.TRANSACTION_TYPE='GRS'))

What I need to do now is update the distribution_group from the pays_history to match the distribution_group from the employee_distribution where the account_id is the same as the account_history.account_id.

Any ideas?

Thanks!

hi,

Try the below Query:

UPDATE EMPLOYEE_DISTRIBUTIONS SET ED.DISTRIBUTION_GROUP = PH.DISTRIBUTION_GROUP FROM 
PAYS_HISTORY PH ,ACCOUNT_HISTORY AH,
EMPLOYEE_DISTRIBUTIONS ED
WHERE PH.CHECK_KEY=AH.CHECK_KEY AND ED.ACCOUNT_ID=AH.ACCOUNT_ID
AND ((PH.CONTRACT_YEAR=2008) AND (AH.TRANSACTION_TYPE='GRS'))

Have a Nice day
:) Shailaja:)

I ran a query to find 1)the current distribution group and corresponding account id number and 2) the historical distribution group and corresponding account id number from checks written in contract year 2008.

Here is the sql statement from my query:

SELECT EMPLOYEE_MASTER.EMPLOYEE_NO, EMPLOYEE_DISTRIBUTIONS.DISTRIBUTION_GROUP, EMPLOYEE_DISTRIBUTIONS.ACCOUNT_ID, PAYS_HISTORY.DISTRIBUTION_GROUP, ACCOUNT_HISTORY.ACCOUNT_ID, ACCOUNT_HISTORY.CHECK_KEY
FROM HRS.ACCOUNT_HISTORY ACCOUNT_HISTORY, HRS.EMPLOYEE_DISTRIBUTIONS EMPLOYEE_DISTRIBUTIONS, HRS.EMPLOYEE_MASTER EMPLOYEE_MASTER, HRS.PAYS_HISTORY PAYS_HISTORY
WHERE EMPLOYEE_DISTRIBUTIONS.EMPLOYEE_NO = EMPLOYEE_MASTER.EMPLOYEE_NO AND PAYS_HISTORY.EMPLOYEE_NO = EMPLOYEE_MASTER.EMPLOYEE_NO AND ACCOUNT_HISTORY.CHECK_KEY = PAYS_HISTORY.CHECK_KEY AND ((PAYS_HISTORY.CONTRACT_YEAR=2008) AND (ACCOUNT_HISTORY.TRANSACTION_TYPE='GRS'))

What I need to do now is update the distribution_group from the pays_history to match the distribution_group from the employee_distribution where the account_id is the same as the account_history.account_id.

Any ideas?

Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.