December 19, 2019 at 12:28 pm
Hi All,
I just cannot seem to get my head around how to apply the correct logic to retrieving a result set based on dates but just one row. I have tried row partition and LIMIT but my results are either too many rows or just incorrect.
I have included the desired results but to explain further where the Info_table and Hist Tables start_dates are a match then the units for that row should be return.
If the Hist_table Hist_Start_Date is greater than Info_table P_start_date then only the units for that row should be retrieved.
Thanks for any help in advance.
Here are my tables:-
Info_Table
REF P_Start_Date P_End_date Name
1 27-OCT-1986 30-NOV-2006 Ann
1 01-DEC-2006 31-APR-2010 Ann
1 01-MAY-2010 NULL Ann
2 01-JAN-2018 10-Aug-2018 Bob
2 11-AUG-2018 NULL Bob
Hist_Table
REF Hist_Start_Date Hist_End_Date Units
1 27-OCT-1986 08-AUG-2002 1000
1 09-AUG-2002 31-DEC-2003 6000
1 01-JAN-2004 31-DEC-2007 9000
1 01-Jan-2008 NULL 12000
2 01-JAN-2018 10-JUL-2018 5000
2 11-JUL-2018 01-AUG-2018 2000
2 25-NOV-2018 NULL 3500
Desired Result
Ref P_Start_Date P_End_Date Name Units
1 27-OCT-1986 30-NOV-2006 Ann 1000
1 01-DEC-2006 31-APR-2010 Ann 9000
1 01-MAY-2010 NULL Ann 12000
2 01-JAN-2018 10-Aug-2018 Bob 5000
2 11-AUG-2018 NULL Bob 3500
December 19, 2019 at 12:52 pm
If you could provide the table DDL and sample data in consumable format, it would be easier for us to help you.
What have you already tried? Is it as simple as joining the two tables on ID = ID and Hist_Start_Date <= P_Start_Date?
John
December 19, 2019 at 3:06 pm
The data is connected by REF but in relation to the dates it is Hist_Start_date >P_Start_Date but the bit I am struggling with is selecting just the immediate row and units what tends to happen is I get all the rows of the dates which are greater than where I just want on.
I will try and provide the code I have tried as I had to make up the data as my own code was extensive with other unnecessary fields.
December 19, 2019 at 3:15 pm
Here is the actual code I tried but failed with but the concept is the same.
I tried with one ref to see if I could get it right with that then others would work
SELECT per.ref, ph.start_date as post_start_date, ph.end_date as post_end_date , A.amount,
ROW_NUMBER() OVER (PARTITION BY emp.ref ORDER BY ph.start_date ASC) rn2
FROM D550M emp
inner join d500m per on per.ref = emp.ref
inner join d580m ph on ph.ref = emp.ref
LEFT join
(
select ref, id, start_date as fpe_start_date, end_date as fpe_end_date, amount
from d555m
WHERE ref='01000082'
and id=9003
ORDER BY start_date DESC
) A on A.ref = emp.ref
where emp.ref='01000082'
and A.fpe_start_date >= ph.start_date order by ph.start_date
and rn2<2
December 19, 2019 at 3:24 pm
Use a CROSS APPLY with a TOP(1).
If you provide consumable data, you'll get tested code.
Drew
PS: I highly recommend NEVER using NULLs when working with intervals. NULL values make the calculations for overlapping intervals much harder. I recommend using specific values instead. Which values depend on your data, but the values I typically use are 1900-01-01 for start dates and 9999-12-30 for end dates.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 19, 2019 at 3:29 pm
One thing I noticed is that the AND A.fpe_start_date >= ph.start_date line will turn your LEFT JOIN into an INNER JOIN. I think you need that to be in your join predicate instead of your WHERE clause. Or do it in the way Drew suggests!
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply