I have 3 tables one with payroll information (payroll), one of pay periods (erpaydates) and one of retirement contribution percents (planeecontrpct). I am trying to find a way to select what the employee's contribution percent (planeecontrpct.contrpct) was for each pay period (erpaydates.enddate) based on planeecontrpct.effdate without creating overlapping records. The result set I am looking for based on the data in the tables is below. I have the basic part of the query complete but I cannot get just a single record for each pay period.
select payroll.ssnum, erpaydates.enddate, payroll.salamt, planeecontrpct.contrpct
from payroll, erpaydates, planeecontrpct
where payroll.erid = erpaydates.erid and payroll.payfreqcd = erpaydates.payfreqcd and payroll.payfreqseqnum = erpaydates.payfreqseqnum
and payroll.paypernum = erpaydates.paypernum and payroll.ssnum = planeecontrpct.ssnum
and erpaydates.begindate between planeecontrpct.effdate and erpaydates.enddate
order by payroll.ssnum, erpaydates.enddate;
payroll.ssnum erpaydates.enddate payroll.salamt planeecontrpct.contrpct
111111111 15-jan-2017 1000 5
111111111 01-feb-2017 1000 5
111111111 15-feb-2017 1000 5
111111111 01-mar-2017 1000 10
111111111 15-mar-2017 1000 10
222222222 15-jan-2017 1000 8
222222222 01-feb-2017 1000 0
222222222 15-feb-2017 1000 0
222222222 01-mar-2017 1000 7
222222222 15-mar-2017 1000 7
What exactly is the logic for determining how the contribution percents align with the pay periods?
The issue with your query is it compares erpaydates start dates to their end dates. Presumably this is always true!
So you could simplify:
and erpaydates.begindate between planeecontrpct.effdate and erpaydates.enddate
To:
and erpaydates.begindate >= planeecontrpct.effdate
Which hopefully makes the problem clearer: you're joining to all the contributions that start before the current period!
You can overcome this by comparing the planeecontrpct.effdate to the pay end date:
select payroll.ssnum,
erpaydates.enddate,
payroll.salamt,
planeecontrpct.contrpct
from payroll
join erpaydates
on payroll.erid = erpaydates.erid
and payroll.payfreqcd = erpaydates.payfreqcd
and payroll.payfreqseqnum = erpaydates.payfreqseqnum
and payroll.paypernum = erpaydates.paypernum
left join planeecontrpct
on payroll.ssnum = planeecontrpct.ssnum
and erpaydates.begindate <= planeecontrpct.effdate
and planeecontrpct.effdate <= erpaydates.enddate
order by payroll.ssnum,
erpaydates.enddate;
SSNUM ENDDATE SALAMT CONTRPCT
111111111 15-JAN-2017 00:00:00 1000 5
111111111 01-FEB-2017 00:00:00 1000
111111111 15-FEB-2017 00:00:00 1000
111111111 01-MAR-2017 00:00:00 1000 10
111111111 15-MAR-2017 00:00:00 1000
222222222 15-JAN-2017 00:00:00 1000 8
222222222 01-FEB-2017 00:00:00 1000 0
222222222 15-FEB-2017 00:00:00 1000
222222222 01-MAR-2017 00:00:00 1000 7
222222222 15-MAR-2017 00:00:00 1000But I'm guessing that's not really what you want. I expect you want fill down the "missing" contributions.
You can do this with last_value, ignoring nulls:
select payroll.ssnum,
erpaydates.enddate,
payroll.salamt,
last_value(planeecontrpct.contrpct) ignore nulls over (
partition by payroll.ssnum order by planeecontrpct.effdate
) contrpct
from payroll
join erpaydates
on payroll.erid = erpaydates.erid
and payroll.payfreqcd = erpaydates.payfreqcd
and payroll.payfreqseqnum = erpaydates.payfreqseqnum
and payroll.paypernum = erpaydates.paypernum
left join planeecontrpct
on payroll.ssnum = planeecontrpct.ssnum
and erpaydates.begindate <= planeecontrpct.effdate
and planeecontrpct.effdate <= erpaydates.enddate
order by payroll.ssnum,
erpaydates.enddate;
SSNUM ENDDATE SALAMT CONTRPCT
111111111 15-JAN-2017 00:00:00 1000 5
111111111 01-FEB-2017 00:00:00 1000 10
111111111 15-FEB-2017 00:00:00 1000 10
111111111 01-MAR-2017 00:00:00 1000 10
111111111 15-MAR-2017 00:00:00 1000 10
222222222 15-JAN-2017 00:00:00 1000 8
222222222 01-FEB-2017 00:00:00 1000 0
222222222 15-FEB-2017 00:00:00 1000 7
222222222 01-MAR-2017 00:00:00 1000 7
222222222 15-MAR-2017 00:00:00 1000 7But is this correct?
I'm not sure.
Employee 111111111 has a new rate starting on 18 Feb. Part way through the period starting 15 Feb.
Should this be split into two periods (15 Feb - 18 Feb & 18 Feb - 1 Mar)? Or kept as one?
If it's the latter, the SQL gets more complicated...