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);









share|improve this question














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.




















    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);









    share|improve this question














    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.


















      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);









      share|improve this question














      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






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      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.
























          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;





          share|improve this answer































            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;





            share|improve this answer























              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


















              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;





              share|improve this answer




























                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;





                share|improve this answer


























                  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;





                  share|improve this answer













                  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;






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 22 '14 at 4:56









                  Richard VivianRichard Vivian

                  1614




                  1614

























                      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;





                      share|improve this answer




























                        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;





                        share|improve this answer


























                          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;





                          share|improve this answer













                          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;






                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Nov 23 '14 at 18:24









                          readymanreadyman

                          1




                          1






























                              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














                              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





















































                              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







                              Popular posts from this blog

                              Liste der Baudenkmale in Friedland (Mecklenburg)

                              Single-Malt-Whisky

                              Czorneboh