July 29, 2008 at 12:15 pm
I have a query that returns 1 to N rows with the following data:
BeginDate, EndDate, NumberOfUnits
I want a query to combine any lines that have contiguous dates and add together the units for those lines. Example:
01/01/2008, 03/15/2008, 7
03/16/2008, 08/24/2008, 9
09/02/2008, 10/25/2008, 30
Should return
01/01/2008, 08/24/2008, 16
09/02/2008, 10/25/2008, 30
The business rules state that if the lines are contiguous or overlap, they can be combined, e.g. 01/01/2008 - 03/15/2008 and 03/01/2008 - 08/02/2008 can be combined. If there is a gap between the end date of the first line and the begin date of the next, they cannot be combined.
Can this be done without using a cursor? I, apparently, am not smart enough to do it 🙂
TIA
John
John Deupree
July 29, 2008 at 3:39 pm
Here's one way to accomplish this:
create table #DateOverlaps (
ID int identity primary key,
StartDate datetime,
EndDate datetime,
Val int)
insert into #dateoverlaps (startdate, enddate, val)
select '01/01/2008', '03/15/2008', 7 union all
select '03/16/2008', '08/24/2008', 9 union all
select '09/02/2008', '10/25/2008', 30
;with
CTE1 (ID, SDate, EDate, DateGroup) as
(select t1.id, t1.startdate, t1.enddate,
row_number() over (order by t1.startdate)
from #dateoverlaps t1
left outer join #dateoverlaps t2
on t1.startdate between t2.startdate and t2.enddate+1
and t1.id != t2.id
where t2.id is null
union all
select t3.id, t3.startdate, t3.enddate, cte1.dategroup
from #dateoverlaps t3
inner join cte1
on t3.startdate between cte1.sdate and cte1.edate+1
and cte1.id != t3.id),
CTE2 (SDate, EDate) as
(select min(sdate), max(edate)
from cte1
group by dategroup)
select
cte2.sdate, cte2.edate,
sum(t4.val) as TotalVal
from cte2
inner join #dateoverlaps t4
on t4.startdate between cte2.sdate and cte2.edate
and t4.enddate between cte2.sdate and cte2.edate
group by cte2.sdate, cte2.edate
It will also work with multiple overlaps. For example, I added:
insert into #dateoverlaps (startdate, enddate, val)
select '08/23/2008', '08/27/2008', 7
Which overlaps the second one in your samples. It correctly ended up with a date range from 1 Jan to 27 Aug, and a total for that range.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2008 at 10:25 am
That's great. Thank you very much. I always seem to have trouble getting my head around recursive queries.
John
John Deupree
July 30, 2008 at 10:32 am
You're welcome.
Those aren't the easiest things to deal with. And it's just barely "not a cursor" even this way. I just can't think of any other way to do it, and this does perform reasonably well in the (somewhat minimal) tests I did.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply