Summarizing Pay Checks Based on Employee's Job Code
0
What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job code with the pay check in which those earnings were earned. Paychecks are issued bi-weekly on Friday with the close of the pay period the Sunday prior, so I am trying to capture the maximum PS_JOB effective date that is <= the pay check date - 5 days. However, my approach may be incorrect.
Here is my attempt at the SQL for this problem:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
CheckViewHrsErn.EARNINGS
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
For some reason with this employee the 02/07/14 pay check is duplicated and I have no idea why. I know that I have to be missing something simple. I consider myself a beginner with SQL so any guidance you can provide will be a huge help!
Sample Data:
PS_JOB:
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('24-JUN-13','DD-MON-RR'),'12345');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('16-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('17-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('23-JUN-14','DD-MON-RR'),'67890');
PS_AL_CHK_HRS_ERN:
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),1665.08);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),24.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),5827.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),2497.63);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-FEB-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),31.92);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('18-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('02-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),1665.09);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),6660.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),26.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),4162.72);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('27-JUN-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),326.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),163.02);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),375.85);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),207.38);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),23.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),168.01);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('03-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),101.25);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('14-NOV-14','DD-MON-RR'),8325.44);
oracle join
asked Nov 22 '14 at 2:12
readymanreadyman
1
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
0
What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job code with the pay check in which those earnings were earned. Paychecks are issued bi-weekly on Friday with the close of the pay period the Sunday prior, so I am trying to capture the maximum PS_JOB effective date that is <= the pay check date - 5 days. However, my approach may be incorrect.
Here is my attempt at the SQL for this problem:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
CheckViewHrsErn.EARNINGS
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
For some reason with this employee the 02/07/14 pay check is duplicated and I have no idea why. I know that I have to be missing something simple. I consider myself a beginner with SQL so any guidance you can provide will be a huge help!
Sample Data:
PS_JOB:
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('24-JUN-13','DD-MON-RR'),'12345');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('16-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('17-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('23-JUN-14','DD-MON-RR'),'67890');
PS_AL_CHK_HRS_ERN:
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),1665.08);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),24.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),5827.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),2497.63);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-FEB-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),31.92);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('18-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('02-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),1665.09);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),6660.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),26.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),4162.72);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('27-JUN-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),326.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),163.02);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),375.85);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),207.38);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),23.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),168.01);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('03-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),101.25);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('14-NOV-14','DD-MON-RR'),8325.44);
oracle join
asked Nov 22 '14 at 2:12
readymanreadyman
1
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
0
0
0
What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job code with the pay check in which those earnings were earned. Paychecks are issued bi-weekly on Friday with the close of the pay period the Sunday prior, so I am trying to capture the maximum PS_JOB effective date that is <= the pay check date - 5 days. However, my approach may be incorrect.
Here is my attempt at the SQL for this problem:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
CheckViewHrsErn.EARNINGS
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
For some reason with this employee the 02/07/14 pay check is duplicated and I have no idea why. I know that I have to be missing something simple. I consider myself a beginner with SQL so any guidance you can provide will be a huge help!
Sample Data:
PS_JOB:
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('24-JUN-13','DD-MON-RR'),'12345');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('16-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('17-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('23-JUN-14','DD-MON-RR'),'67890');
PS_AL_CHK_HRS_ERN:
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),1665.08);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),24.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),5827.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),2497.63);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-FEB-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),31.92);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('18-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('02-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),1665.09);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),6660.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),26.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),4162.72);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('27-JUN-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),326.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),163.02);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),375.85);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),207.38);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),23.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),168.01);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('03-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),101.25);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('14-NOV-14','DD-MON-RR'),8325.44);
oracle join
asked Nov 22 '14 at 2:12
readymanreadyman
1
What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job code with the pay check in which those earnings were earned. Paychecks are issued bi-weekly on Friday with the close of the pay period the Sunday prior, so I am trying to capture the maximum PS_JOB effective date that is <= the pay check date - 5 days. However, my approach may be incorrect.
Here is my attempt at the SQL for this problem:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
CheckViewHrsErn.EARNINGS
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
For some reason with this employee the 02/07/14 pay check is duplicated and I have no idea why. I know that I have to be missing something simple. I consider myself a beginner with SQL so any guidance you can provide will be a huge help!
Sample Data:
PS_JOB:
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('24-JUN-13','DD-MON-RR'),'12345');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('16-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('17-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('23-JUN-14','DD-MON-RR'),'67890');
PS_AL_CHK_HRS_ERN:
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),1665.08);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),24.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),5827.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),2497.63);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-FEB-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),31.92);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('18-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('02-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),1665.09);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),6660.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),26.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),4162.72);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('27-JUN-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),326.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),163.02);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),375.85);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),207.38);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),23.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),168.01);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('03-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),101.25);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('14-NOV-14','DD-MON-RR'),8325.44);
oracle join
oracle join
asked Nov 22 '14 at 2:12
readymanreadyman
1
asked Nov 22 '14 at 2:12
readymanreadyman
1
asked Nov 22 '14 at 2:12
readymanreadyman
1
asked Nov 22 '14 at 2:12
readymanreadyman
1
asked Nov 22 '14 at 2:12
readymanreadyman
1
1
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause.
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
add a comment |
0
I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '01088844'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
AND JobDta.EFFSEQ =
(
SELECT MAX ( JobMaxEffSeq.EFFSEQ ) FROM PS_JOB JobMaxEffSeq
WHERE JobMaxEffSeq.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffSeq.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 23 '14 at 18:24
readymanreadyman
1
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f83332%2fsummarizing-pay-checks-based-on-employees-job-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause.
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
add a comment |
0
Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause.
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
add a comment |
0
0
0
Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause.
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause.
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
1614
add a comment |
add a comment |
0
I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '01088844'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
AND JobDta.EFFSEQ =
(
SELECT MAX ( JobMaxEffSeq.EFFSEQ ) FROM PS_JOB JobMaxEffSeq
WHERE JobMaxEffSeq.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffSeq.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 23 '14 at 18:24
readymanreadyman
1
add a comment |
0
I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '01088844'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
AND JobDta.EFFSEQ =
(
SELECT MAX ( JobMaxEffSeq.EFFSEQ ) FROM PS_JOB JobMaxEffSeq
WHERE JobMaxEffSeq.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffSeq.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 23 '14 at 18:24
readymanreadyman
1
add a comment |
0
0
0
I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '01088844'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
AND JobDta.EFFSEQ =
(
SELECT MAX ( JobMaxEffSeq.EFFSEQ ) FROM PS_JOB JobMaxEffSeq
WHERE JobMaxEffSeq.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffSeq.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 23 '14 at 18:24
readymanreadyman
1
I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '01088844'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
AND JobDta.EFFSEQ =
(
SELECT MAX ( JobMaxEffSeq.EFFSEQ ) FROM PS_JOB JobMaxEffSeq
WHERE JobMaxEffSeq.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffSeq.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 23 '14 at 18:24
readymanreadyman
1
answered Nov 23 '14 at 18:24
readymanreadyman
1
answered Nov 23 '14 at 18:24
readymanreadyman
1
answered Nov 23 '14 at 18:24
readymanreadyman
1
1
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f83332%2fsummarizing-pay-checks-based-on-employees-job-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
