Summarizing Pay Checks Based on Employee's Job CodeGrouping results based on link tableCreating a job...
Numerical value of Determinant far from what it is supposed to be
Can the Witch Sight warlock invocation see through the Mirror Image spell?
How do I increase the number of TTY consoles?
cannot log in to the server after changing SSH port
Is it a Cyclops number? "Nobody" knows!
What do you call someone who likes to pick fights?
What is the "determinant" of two vectors?
Are small insurances worth it?
Why does Central Limit Theorem break down in my simulation?
Rationale to prefer local variables over instance variables?
Traveling to heavily polluted city, what practical measures can I take to minimize impact?
How do spaceships determine each other's mass in space?
Writing text next to a table
How do you make a gun that shoots melee weapons and/or swords?
ESPP--any reason not to go all in?
Why do we say 'Pairwise Disjoint', rather than 'Disjoint'?
Giving a career talk in my old university, how prominently should I tell students my salary?
If nine coins are tossed, what is the probability that the number of heads is even?
Why restrict private health insurance?
Can't make sense of a paragraph from Lovecraft
What will happen if my luggage gets delayed?
Would those living in a "perfect society" not understand satire
What would be the most expensive material to an intergalactic society?
Use Mercury as quenching liquid for swords?
Summarizing Pay Checks Based on Employee's Job Code
Grouping results based on link tableCreating a job scheduled based on a specific timezoneGetting multiplication of column values on a joined table based on conditionCapturing Table Changes without killing the serverHow to reuse code in Oracle Script?Proper SQL code for retrieving all rows from three tables, one of them is like a man-in-the-middleUpdate values in one table based on contents of anotherJoin based on dateGroup by checks constraints are possible?Internal error code, kksgaItLvl_Setup
0
What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job code with the pay check in which those earnings were earned. Paychecks are issued bi-weekly on Friday with the close of the pay period the Sunday prior, so I am trying to capture the maximum PS_JOB effective date that is <= the pay check date - 5 days. However, my approach may be incorrect.
Here is my attempt at the SQL for this problem:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
CheckViewHrsErn.EARNINGS
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
For some reason with this employee the 02/07/14 pay check is duplicated and I have no idea why. I know that I have to be missing something simple. I consider myself a beginner with SQL so any guidance you can provide will be a huge help!
Sample Data:
PS_JOB:
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('24-JUN-13','DD-MON-RR'),'12345');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('16-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('17-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('23-JUN-14','DD-MON-RR'),'67890');
PS_AL_CHK_HRS_ERN:
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),1665.08);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),24.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),5827.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),2497.63);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-FEB-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),31.92);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('18-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('02-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),1665.09);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),6660.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),26.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),4162.72);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('27-JUN-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),326.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),163.02);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),375.85);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),207.38);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),23.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),168.01);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('03-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),101.25);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('14-NOV-14','DD-MON-RR'),8325.44);
oracle join
asked Nov 22 '14 at 2:12
readymanreadyman
1
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
0
What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job code with the pay check in which those earnings were earned. Paychecks are issued bi-weekly on Friday with the close of the pay period the Sunday prior, so I am trying to capture the maximum PS_JOB effective date that is <= the pay check date - 5 days. However, my approach may be incorrect.
Here is my attempt at the SQL for this problem:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
CheckViewHrsErn.EARNINGS
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
For some reason with this employee the 02/07/14 pay check is duplicated and I have no idea why. I know that I have to be missing something simple. I consider myself a beginner with SQL so any guidance you can provide will be a huge help!
Sample Data:
PS_JOB:
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('24-JUN-13','DD-MON-RR'),'12345');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('16-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('17-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('23-JUN-14','DD-MON-RR'),'67890');
PS_AL_CHK_HRS_ERN:
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),1665.08);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),24.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),5827.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),2497.63);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-FEB-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),31.92);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('18-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('02-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),1665.09);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),6660.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),26.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),4162.72);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('27-JUN-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),326.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),163.02);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),375.85);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),207.38);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),23.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),168.01);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('03-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),101.25);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('14-NOV-14','DD-MON-RR'),8325.44);
oracle join
asked Nov 22 '14 at 2:12
readymanreadyman
1
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
0
0
0
What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job code with the pay check in which those earnings were earned. Paychecks are issued bi-weekly on Friday with the close of the pay period the Sunday prior, so I am trying to capture the maximum PS_JOB effective date that is <= the pay check date - 5 days. However, my approach may be incorrect.
Here is my attempt at the SQL for this problem:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
CheckViewHrsErn.EARNINGS
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
For some reason with this employee the 02/07/14 pay check is duplicated and I have no idea why. I know that I have to be missing something simple. I consider myself a beginner with SQL so any guidance you can provide will be a huge help!
Sample Data:
PS_JOB:
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('24-JUN-13','DD-MON-RR'),'12345');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('16-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('17-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('23-JUN-14','DD-MON-RR'),'67890');
PS_AL_CHK_HRS_ERN:
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),1665.08);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),24.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),5827.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),2497.63);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-FEB-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),31.92);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('18-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('02-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),1665.09);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),6660.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),26.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),4162.72);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('27-JUN-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),326.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),163.02);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),375.85);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),207.38);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),23.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),168.01);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('03-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),101.25);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('14-NOV-14','DD-MON-RR'),8325.44);
oracle join
asked Nov 22 '14 at 2:12
readymanreadyman
1
What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job code with the pay check in which those earnings were earned. Paychecks are issued bi-weekly on Friday with the close of the pay period the Sunday prior, so I am trying to capture the maximum PS_JOB effective date that is <= the pay check date - 5 days. However, my approach may be incorrect.
Here is my attempt at the SQL for this problem:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
CheckViewHrsErn.EARNINGS
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
For some reason with this employee the 02/07/14 pay check is duplicated and I have no idea why. I know that I have to be missing something simple. I consider myself a beginner with SQL so any guidance you can provide will be a huge help!
Sample Data:
PS_JOB:
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('24-JUN-13','DD-MON-RR'),'12345');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('20-JAN-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('16-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('17-FEB-14','DD-MON-RR'),'67890');
Insert into PS_JOB (EMPLID,EFFDT,JOBCODE) values ('12345678',to_date('23-JUN-14','DD-MON-RR'),'67890');
PS_AL_CHK_HRS_ERN:
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),1665.08);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('10-JAN-14','DD-MON-RR'),24.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),5827.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('24-JAN-14','DD-MON-RR'),2497.63);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-FEB-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-FEB-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',0,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),83.6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',2,to_date('07-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('07-MAR-14','DD-MON-RR'),64652.81);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('21-MAR-14','DD-MON-RR'),31.92);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('04-APR-14','DD-MON-RR'),6);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('18-APR-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('02-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),1665.09);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('16-MAY-14','DD-MON-RR'),6660.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('30-MAY-14','DD-MON-RR'),26.35);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),4162.72);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('13-JUN-14','DD-MON-RR'),3330.18);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('27-JUN-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('11-JUL-14','DD-MON-RR'),326.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('25-JUL-14','DD-MON-RR'),163.02);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('08-AUG-14','DD-MON-RR'),375.85);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),207.38);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('22-AUG-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),23.47);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('05-SEP-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),168.01);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('19-SEP-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('03-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),832.54);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('17-OCT-14','DD-MON-RR'),7492.9);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),101.25);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('31-OCT-14','DD-MON-RR'),8325.44);
Insert into PS_AL_CHK_HRS_ERN (EMPLID,ENTRY_NBR,CHECK_DT,EARNINGS) values ('12345678',1,to_date('14-NOV-14','DD-MON-RR'),8325.44);
oracle join
oracle join
asked Nov 22 '14 at 2:12
readymanreadyman
1
asked Nov 22 '14 at 2:12
readymanreadyman
1
asked Nov 22 '14 at 2:12
readymanreadyman
1
asked Nov 22 '14 at 2:12
readymanreadyman
1
asked Nov 22 '14 at 2:12
readymanreadyman
1
1
bumped to the homepage by Community♦ 2 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♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause.
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
add a comment |
0
I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '01088844'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
AND JobDta.EFFSEQ =
(
SELECT MAX ( JobMaxEffSeq.EFFSEQ ) FROM PS_JOB JobMaxEffSeq
WHERE JobMaxEffSeq.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffSeq.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 23 '14 at 18:24
readymanreadyman
1
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f83332%2fsummarizing-pay-checks-based-on-employees-job-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause.
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
add a comment |
0
Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause.
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
add a comment |
0
0
0
Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause.
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
Looks like you have multiple entries in PS_AL_CHK_HRS_ERN that meet the criteria. To Summarize you will have to use SUM(CheckViewHrsErn.EARNINGS) and add a GROUP BY clause.
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '12345678'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT = (
SELECT MAX( JobDtaMax.EFFDT )
FROM PS_JOB JobDtaMax
WHERE JobDtaMax.EMPLID = CheckViewHrsErn.EMPLID
AND JobDtaMax.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
answered Nov 22 '14 at 4:56
Richard VivianRichard Vivian
1614
1614
add a comment |
add a comment |
0
I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '01088844'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
AND JobDta.EFFSEQ =
(
SELECT MAX ( JobMaxEffSeq.EFFSEQ ) FROM PS_JOB JobMaxEffSeq
WHERE JobMaxEffSeq.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffSeq.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 23 '14 at 18:24
readymanreadyman
1
add a comment |
0
I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '01088844'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
AND JobDta.EFFSEQ =
(
SELECT MAX ( JobMaxEffSeq.EFFSEQ ) FROM PS_JOB JobMaxEffSeq
WHERE JobMaxEffSeq.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffSeq.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 23 '14 at 18:24
readymanreadyman
1
add a comment |
0
0
0
I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '01088844'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
AND JobDta.EFFSEQ =
(
SELECT MAX ( JobMaxEffSeq.EFFSEQ ) FROM PS_JOB JobMaxEffSeq
WHERE JobMaxEffSeq.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffSeq.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 23 '14 at 18:24
readymanreadyman
1
I was able to figure out my own problem. The HR system is based on Peoplesoft and it includes something called an effective sequence. For this specific employee they had more than one effective sequence on the 01/20/14 effective date which was causing the duplication. Here is the finished SQL:
SELECT
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT,
SUM(CheckViewHrsErn.EARNINGS)
FROM PS_AL_CHK_HRS_ERN CheckViewHrsErn
LEFT JOIN PS_JOB JobDta ON JobDta.EMPLID = CheckViewHrsErn.EMPLID
WHERE
CheckViewHrsErn.EMPLID = '01088844'
AND CheckViewHrsErn.ENTRY_NBR <> 0
AND CheckViewHrsErn.EARNINGS <> 0
AND CheckViewHrsErn.CHECK_DT >= TO_DATE('2014-01-01', 'yyyy-mm-dd')
AND JobDta.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
AND JobDta.EFFSEQ =
(
SELECT MAX ( JobMaxEffSeq.EFFSEQ ) FROM PS_JOB JobMaxEffSeq
WHERE JobMaxEffSeq.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffSeq.EFFDT =
(
SELECT MAX ( JobMaxEffdt.EFFDT ) FROM PS_JOB JobMaxEffdt
WHERE JobMaxEffdt.EMPLID = CheckViewHrsErn.EMPLID
AND JobMaxEffdt.EFFDT <= CheckViewHrsErn.CHECK_DT - 5
)
)
GROUP BY
CheckViewHrsErn.EMPLID,
JobDta.JOBCODE,
CheckViewHrsErn.CHECK_DT
ORDER BY CheckViewHrsErn.EMPLID ASC, CheckViewHrsErn.CHECK_DT ASC;
answered Nov 23 '14 at 18:24
readymanreadyman
1
answered Nov 23 '14 at 18:24
readymanreadyman
1
answered Nov 23 '14 at 18:24
readymanreadyman
1
answered Nov 23 '14 at 18:24
readymanreadyman
1
1
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f83332%2fsummarizing-pay-checks-based-on-employees-job-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
