asked
Generate date ranges from existing date ranges
I am new to SQL Server. I am trying to get the continous date ranges from existing ones. I have records with the following ranges based on category category startdate enddate 10 1/1/2010 10/30/2010 10 5/3/2010 10/30/2010 11 1/1/2010 10/30/2011 11 5/3/2010 10/30/2010 the result should show category startdate enddate 10 1/1/2010 5/2/2010 10 5/3/2010 10/30/2010 11 1/1/2010 5/2/2010 11 5/3/2010 10/30/2010 11 10/31/2010 10/30/2011 I try the following. But missing the continous ranges... any advice? DECLARE @TMP1 TABLE (CategoryID varchar(5), StartDate varchar(50), EndDate varchar(50)) INSERT @TMP1 (CategoryID, startdate, enddate) VALUES ('10', '1/1/2010 17:49:49', '10/30/2010 17:59:59'), ('10', '5/3/2010 17:49:49', '10/30/2010 17:59:59'), ('11', '1/1/2010 17:49:49', '10/30/2011 17:59:59'), ('11', '5/3/2010 17:49:49', '10/30/2010 17:59:59') SELECT t1.CategoryID, t1.col1 AS StartDate, MIN(t2.col1) AS EndDate FROM (SELECT col1 = DATEADD(SECOND, 1, StartDate), CategoryID FROM @TMP1 tbl1 WHERE NOT EXISTS ( SELECT * FROM @TMP1 tbl2 WHERE tbl2.StartDate = DATEADD(SECOND, 1, tbl1.StartDate) AND CategoryID = tbl1.CategoryID ) AND StartDate <> (SELECT MAX(StartDate) FROM @TMP1 WHERE CategoryID = tbl1.CategoryID)) t1 LEFT OUTER JOIN (SELECT col1 = DATEADD(SECOND, -1, StartDate), CategoryID FROM @TMP1 tbl1 WHERE NOT EXISTS ( SELECT * FROM @TMP1 tbl2 WHERE tbl1.StartDate = DATEADD(SECOND,1,tbl2.StartDate) AND CategoryID = tbl2.CategoryId ) AND StartDate <> (SELECT MIN(StartDate) FROM @TMP1 WHERE CategoryId=tbl1.CategoryID)) t2 ON t1.col1 <= t2.col1 AND t1.CategoryID = t2.CategoryID GROUP BY t1.col1, t1.CategoryID ORDER BY t1.CategoryID, t1.col1
I'm confused. Why does Category 11 get three records based on two, but Category 10 only gets two records based on two?
Category 11 has two date ranges one of which is completely within the other.
Hmm. I think that the approach I would follow would be to create an intermediate table / view / CTE containing just two fields - category & date - and populate that with select category, startdate ... union select category, enddate ... Then feed the output of that into a [partitioned query][1], and then join that with itself. And the reason I'm just thinking out loud rather than doing it? I expect Oleg will be along in about 30 seconds from now to provide the answer! Several hours later, and I've slept on it... My answer: declare @tbl table ( Category int, StartDate datetime, EndDate datetime ) insert into @tbl SELECT 10, '2010-01-01', '2010-10-30' UNION ALL SELECT 10, '2010-05-03', '2010-10-30' UNION ALL SELECT 11, '2010-01-01', '2011-10-30' UNION ALL SELECT 11, '2010-05-03', '2010-10-30' UNION ALL SELECT 11, '2010-10-31', '2011-01-30' -- Scot's addition ; with cte AS( SELECT Category, StartDate AS dDate FROM @tbl UNION SELECT Category, EndDate AS dDate FROM @tbl ), cte2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Category ORDER BY dDate) AS RowNum FROM cte ), cte3 AS ( SELECT t1.Category, t1.dDate AS DateFrom, t1.RowNum, t2.dDate AS DateTo FROM cte2 t1 LEFT JOIN cte2 t2 ON t1.Category = t2.Category AND t1.RowNum = t2.RowNum-1 ), cte4 AS ( SELECT Category, MAX(DateFrom) AS MaxDate FROM cte3 GROUP BY Category ) SELECT cte3.Category, DateFrom, CASE WHEN DateTo=MaxDate THEN DateTo ELSE DATEADD(DAY, -1, DateTo) END AS DateTo FROM cte3 LEFT JOIN cte4 ON cte3.Category = cte4.Category WHERE cte3.DateTo IS NOT NULL With thanks to Squirrel for the first few lines (up as far as the introduction of cte1). The output, with the original input: 10 2010-01-01 00:00:00.000 2010-05-02 00:00:00.000 10 2010-05-03 00:00:00.000 2010-10-30 00:00:00.000 11 2010-01-01 00:00:00.000 2010-05-02 00:00:00.000 11 2010-05-03 00:00:00.000 2010-10-29 00:00:00.000 11 2010-10-30 00:00:00.000 2011-10-30 00:00:00.000 And with Scot's line: 10 2010-01-01 00:00:00.000 2010-05-02 00:00:00.000 10 2010-05-03 00:00:00.000 2010-10-30 00:00:00.000 11 2010-01-01 00:00:00.000 2010-05-02 00:00:00.000 11 2010-05-03 00:00:00.000 2010-10-29 00:00:00.000 11 2010-10-30 00:00:00.000 2010-10-30 00:00:00.000 11 2010-10-31 00:00:00.000 2011-01-29 00:00:00.000 11 2011-01-30 00:00:00.000 2011-10-30 00:00:00.000 Which I reckon should be acceptable... OK, it's almost certainly possible to do it in one fewer CTEs, but this is, I think, easy to read... [1]:
http://msdn.microsoft.com/en-us/library/ms189461.aspx
+1 - Like that solution, I sat for 10 minutes trying to get something in that direction to work. Sleeping on it obviously helps :) Lets hope that the real data is on a nicely indexed table. I would hate to see this on a large heap - six table scans with sorting would be a world of pain. P.S. that pushes you ahead of me again ;)
declare @tbl table ( category int, startdate datetime, enddate datetime ) insert into @tbl select 10, '1/1/2010', '10/30/2010' union all select 10, '5/3/2010', '10/30/2010' union all select 11, '1/1/2010', '10/30/2011' union all select 11, '5/3/2010', '10/30/2010' ; with cte as ( select category, dte = startdate, f = 'S' from @tbl union select category, dte = enddate, f = 'E' from @tbl ), cte2 as ( select *, row_no = row_number() over (partition by category order by dte) from cte ) select category = c1.category, startdate = case when c1.f = 'E' then dateadd(day, 1, c1.dte) else c1.dte end, enddate = case when c2.f = 'S' then dateadd(day, -1, c2.dte) else c2.dte end from cte2 c1 inner join cte2 c2 on c1.category = c2.category and c1.row_no = c2.row_no - 1 /* category startdate enddate ----------- ------------------------------------------------------ ------------------------------------------------------ 10 2010-01-01 00:00:00.000 2010-05-02 00:00:00.000 10 2010-05-03 00:00:00.000 2010-10-30 00:00:00.000 11 2010-01-01 00:00:00.000 2010-05-02 00:00:00.000 11 2010-05-03 00:00:00.000 2010-10-30 00:00:00.000 11 2010-10-31 00:00:00.000 2011-10-30 00:00:00.000 (5 row(s) affected) */
Hi Squirrel, it's close: add 11, '10/31/2010', '1/30/2011' to the table and check the results
Hi @Squirrel. I've nicked the first few lines and had a go myself based on my own thought processes. And I think it caters for @Scot's little quirk too.
After a good night sleep, i came up with a simpler query that will cater to Scot's quirk :) declare @tbl table ( category int, startdate datetime, enddate datetime ) insert into @tbl select 10, '2010-01-01', '2010-10-30' union all select 10, '2010-05-03', '2010-10-30' union all select 11, '2010-01-01', '2011-10-30' union all select 11, '2010-05-03', '2010-10-30' union all select 11, '2010-10-31', '2011-01-30' -- scot's addition ; with cte as ( select category, dte = startdate from @tbl union select category, dte = enddate from @tbl ), cte2 as ( select *, row_no = row_number() over (partition by category order by dte) from cte ) select category = c1.category, startdate = c1.dte, enddate = case when c2.row_no < max(c2.row_no) over (partition by c2.category) then dateadd(day, -1, c2.dte) else c2.dte end from cte2 c1 inner join cte2 c2 on c1.category = c2.category and c1.row_no = c2.row_no - 1 /* category startdate enddate ----------- ------------------------ ----------------------- 10 2010-01-01 00:00:00.000 2010-05-02 00:00:00.000 10 2010-05-03 00:00:00.000 2010-10-30 00:00:00.000 11 2010-01-01 00:00:00.000 2010-05-02 00:00:00.000 11 2010-05-03 00:00:00.000 2010-10-29 00:00:00.000 11 2010-10-30 00:00:00.000 2010-10-30 00:00:00.000 11 2010-10-31 00:00:00.000 2011-01-29 00:00:00.000 11 2011-01-30 00:00:00.000 2011-10-30 00:00:00.000 (7 row(s) affected) */
