Main » 2012 » July » 20 » SQL Start and Enddate filter.
3:57 PM SQL Start and Enddate filter. |
I've been working on a particularly poorly designed third party system. In this system child records have a start date and an end date.
The system has a flaw in which while allowing one to many links from parent to child records (needed for history), the business logic does not impose the required behaviour. For instance there can be more than one open (null end date) child record when only one should be permitted and all child records can be closed (non null end date) when a child record should only be closed on the creation of a new open record.
The reports that I have been tasked with creating therefore required a new filter type on these child records.
The filter was needed to return only the open child record with the latest start date or if there is no open child records the record with the latest end date.
To do this I split the situation. Firstly we want only the most recent open records. This is fairly simple.
(Select * from Child where enddate is null) as a
Inner join
(Select id, max(startdate) as startdate from Child where enddate is null group by id) as b
on a.id = b.id and a.startdate = b.startdate
Next we want the most recent end date again fairly simple using the method above.
(Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate
We can than make a union of these that will almost solve our problem.
Select x.* from (
(Select * from Child where enddate is null) as a
Inner join
(Select id, max(startdate) as startdate from Child where enddate is null group by id) as b
on a.id = b.id and a.startdate = b.startdate
) as x
union
select y.* from (
(Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate
) as y
I say almost as their will still be 2 records where open and ended child records are present. So we must apply the conditional filter I detailed in my previous post. The script becoming.
Select Cond.* from
((Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate
We can than make a union of these that will almost solve our problem.
Select x.* from (
(Select * from Child where enddate is null) as a
Inner join
(Select id, max(startdate) as startdate from Child where enddate is null group by id) as b
on a.id = b.id and a.startdate = b.startdate
) as x
union
select y.* from (
(Select * from Child where enddate is not null) as a
Inner join
(Select id, max(enddate) as enddate from Child where enddate is not null group by id) as b
on a.id = b.id and a. enddate = b. enddate
) as y) as cond
left outer join
( select id from child where EndDATE is null group by id) AS condFilter
on cond.id = condFilter.id where ((cond.EndDate is null and condFilter.id is not null) or (cond.EndDate is not null and condFilter.id is null))
|
|
Views: 7861 |
Added by: The_Kat
| Rating: 0.0/0 |
|
|
| Login form |
|
 |
|
