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







share|improve this question














bumped to the homepage by Community♦ 17 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);





    oracle join







    share|improve this question














    bumped to the homepage by Community♦ 17 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);





      oracle join







      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




      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♦ 17 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♦ 17 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

                              SQL Server 17 - Attemping to backup to remote NAS but Access is denied

                              Image
                              .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0; } 0 I was attemping to use Ola Hallengrens scripts to backup to a remote NAS, but it was failing and not giving any direct errors, so I tried doing it manually using Server Management Studio and got the following error: TITLE: Microsoft SQL Server Management Studio Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476 ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The operating system returned the error '0x80070005(Access is denied.)&#
                              Read more

                              Always On Availability groups resolving state after failover - Remote harden of transaction...

                              Image
                              0 My explanation will be opinion based as it is based on my findings. If more information is needed, I am always happy to add it. The setup We have 4 SQL Server 2016 Instances, and all 4 underlying servers are in a WSFC (In my eyes a strange setup since Always On AG's are 2 by 2. But that is a different question.) 4 nodes + Fileshare witness Version: 13.0.5026.0 (all nodes) The instances that are having the failover issues are instances 1 & 2. They have 3 AG's between them. AG1 is primary on instance 1 and is having the issue. AG2 and AG3 are primary on instance 2. Instances 3 and 4 have their own separate AG's (2). AG1's settings The week before Last week, after checking for long running queries (none) and the synchronization state (green accross the board) we initiated a
                              Read more

                              Restoring from pg_dump with foreign key constraints

                              Image
                              5 2 In restoring a database from a pg_dump , a number of errors are being generated and the whole table is subsequently being ignored. An example: ERROR: insert or update on table "channelproducts" violates foreign key constraint "fk_rails_dfaae373a5" DETAIL: Key (channel_id)=(1) is not present in table "channels". Interestingly enough, I've noted that all these instances are popping up because of the sequence of loading. channels is after channelproducts both alphabetically and in the file and thus I can understand why postgres complains about having to create a child without a parent. caveat: the foreign key is being generated automatically by rails 4.2: I could remove the problem at the source but that still does not really solve the problem... version: PostgreSQL
                              Read more