Summarizing Pay Checks Based on Employee’s Job Code

join;oracle

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

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