March 24, 2014 at 7:04 pm
How can I set the following overlapping date rages / count to a contiguous date range / count in SQL:
StartDate EndDate NumberPerDay
10/1/2007 2/29/2008 2
3/1/2008 12/14/2009 3
3/1/2008 12/31/2009 4
12/15/2009 12/31/2078 2
To:
StartDate EndDate TotalNumberPerDay
10/1/2007 2/29/2008 2
3/1/2008 12/14/2009 7
12/15/2008 12/31/2009 6
1/1/2010 12/31/2078 2
March 25, 2014 at 8:53 am
First, since you are new to ssc, I took the time to setup the data to work the problem. In this case I simply used a CTE (TestData) to provide the data for the script. I could have populated a temporary table, table variable or a user table. I would recommend reading the first article I reference below in my signature block as it will show you what you need to post and how to do it in the future which will make it easier for others to help you with your questions.
There is a lot going on in the script below. First of all, I use a dynamic tally table in the code to generate values to create a sequence of dates based on the begin and end dates of the data. Second I use a gaps and islands strategy to identify the appropriate groupings so I can pull the data back into a StartDate/EndDate grouping.
Please study closely the code and be sure you understand what it is doing before you use it in a production environment. To help with this you should read the following articles by Jeff Moden:
The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]
Group Islands of Contiguous Dates (SQL Spackle)[/url]
with TestData as (
select
StartDate,
EndDate,
NumberPerDay
from
(values
('20071001','20080229',2),
('20080301','20091214',3),
('20080301','20091231',4),
('20091215','20781231',2)
)dt(StartDate,EndDate,NumberPerDay))
,e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n))
,e2(n) as (select 1 from e1 a cross join e1 b)
,e4(n) as (select 1 from e2 a cross join e2 b)
,eTally(n) as (select 0 union all select row_number() over (order by (select null)) from e2 a cross join e4 b),
BaseData as (
select
NewDate = dateadd(day,et.n,td.StartDate),
sum(td.NumberPerDay) TotalNumberPerDay
from
TestData td
cross apply (select top(datediff(dd,td.StartDate,td.EndDate) + 1) n from eTally)et(n)
group by
dateadd(day,et.n,td.StartDate)
),
Level1 as (
select
bd.NewDate,
bd.TotalNumberPerDay,
rn = row_number() over (partition by bd.TotalNumberPerDay order by bd.NewDate)
from
BaseData bd
)
select
min(NewDate) StartDate,
max(NewDate) EndDate,
TotalNumberPerDay
from
Level1
group by
dateadd(day,-rn,NewDate),
TotalNumberPerDay
order by
StartDate;
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply