Create consecutive day groups that match your criteria

Question

I have the following data structure in SQL Server:

ID Date Allocation 1, 2012-01-01, 0 2, 2012-01-02, 2 3, 2012-01-03, 0 4, 2012-01-04, 0 5, 2012-01-05, 0 6, 2012-01-06, 5 

and etc.

I need to do all consecutive daily periods, where Allocation = 0, and in the following form:

 Start Date End Date DayCount 2012-01-01 2012-01-01 1 2012-01-03 2012-01-05 3 

and etc.

Is it possible to do this in SQL, and if so, how?

Answer 1

In this answer, I will assume that the "id" field sequentially groups the rows when sorting by increasing the date, as happens in the example data. (Such a column can be created if it does not exist).

This is an example of the method described here and here .

1) Attach the table to yourself with adjacent "id" values. These are pairs of adjacent lines. Select the rows in which the distribution field has changed. Store the result in a temporary table, also keeping the current index.

 SET @idx = 0; CREATE TEMPORARY TABLE boundaries SELECT (@idx := @idx + 1) AS idx, a1.date AS prev_end, a2.date AS next_start, a1.allocation as allocation FROM allocations a1 JOIN allocations a2 ON (a2.id = a1.id + 1) WHERE a1.allocation != a2.allocation; 

This gives you a table that has “end of previous period”, “start of next period” and “distribution value” in previous period “in each row:

 +------+------------+------------+------------+ | idx | prev_end | next_start | allocation | +------+------------+------------+------------+ | 1 | 2012-01-01 | 2012-01-02 | 0 | | 2 | 2012-01-02 | 2012-01-03 | 2 | | 3 | 2012-01-05 | 2012-01-06 | 0 | +------+------------+------------+------------+ 

2) We need the beginning and end of each period on the same line, so we need to combine the adjacent lines again. Do this by creating a second temporary table of type boundaries , but having an idx field larger:

 +------+------------+------------+ | idx | prev_end | next_start | +------+------------+------------+ | 2 | 2012-01-01 | 2012-01-02 | | 3 | 2012-01-02 | 2012-01-03 | | 4 | 2012-01-05 | 2012-01-06 | +------+------------+------------+ 

Now join the idx field and we get the answer:

 SELECT boundaries2.next_start AS start, boundaries.prev_end AS end, allocation FROM boundaries JOIN boundaries2 USING(idx); +------------+------------+------------+ | start | end | allocation | +------------+------------+------------+ | 2012-01-02 | 2012-01-02 | 2 | | 2012-01-03 | 2012-01-05 | 0 | +------------+------------+------------+ 

** Please note that this answer correctly accepts "internal" periods, but skips the two "extreme" periods, where distribution = 0 at the beginning and distribution = 5 at the end. They can be used with UNION suggestions, but I wanted to introduce the main idea without this complication.

Answer 2

The following will be one way to do this. The essence of this decision

• Use CTE to get a list of all consecutive starts and endings with Allocation = 0
• Use the window function ROW_NUMBER to assign series based on start and end dates.
• Select only those records where both ROW_NUMBERS are 1.
• Use DATEDIFF to calculate DayCount

SQL statement

 ;WITH r AS ( SELECT StartDate = Date, EndDate = Date FROM YourTable WHERE Allocation = 0 UNION ALL SELECT r.StartDate, q.Date FROM r INNER JOIN YourTable q ON DATEDIFF(dd, r.EndDate, q.Date) = 1 WHERE q.Allocation = 0 ) SELECT [Start Date] = s.StartDate , [End Date ] = s.EndDate , [DayCount] = DATEDIFF(dd, s.StartDate, s.EndDate) + 1 FROM ( SELECT * , rn1 = ROW_NUMBER() OVER (PARTITION BY StartDate ORDER BY EndDate DESC) , rn2 = ROW_NUMBER() OVER (PARTITION BY EndDate ORDER BY StartDate ASC) FROM r ) s WHERE s.rn1 = 1 AND s.rn2 = 1 OPTION (MAXRECURSION 0) 

Test script

 ;WITH q (ID, Date, Allocation) AS ( SELECT * FROM (VALUES (1, '2012-01-01', 0) , (2, '2012-01-02', 2) , (3, '2012-01-03', 0) , (4, '2012-01-04', 0) , (5, '2012-01-05', 0) , (6, '2012-01-06', 5) ) a (a, b, c) ) , r AS ( SELECT StartDate = Date, EndDate = Date FROM q WHERE Allocation = 0 UNION ALL SELECT r.StartDate, q.Date FROM r INNER JOIN q ON DATEDIFF(dd, r.EndDate, q.Date) = 1 WHERE q.Allocation = 0 ) SELECT s.StartDate, s.EndDate, DATEDIFF(dd, s.StartDate, s.EndDate) + 1 FROM ( SELECT * , rn1 = ROW_NUMBER() OVER (PARTITION BY StartDate ORDER BY EndDate DESC) , rn2 = ROW_NUMBER() OVER (PARTITION BY EndDate ORDER BY StartDate ASC) FROM r ) s WHERE s.rn1 = 1 AND s.rn2 = 1 OPTION (MAXRECURSION 0) 

Answer 3

An alternative way with CTE, but without ROW_NUMBER (),

Sample data:

 if object_id('tempdb..#tab') is not null drop table #tab create table #tab (id int, date datetime, allocation int) insert into #tab select 1, '2012-01-01', 0 union select 2, '2012-01-02', 2 union select 3, '2012-01-03', 0 union select 4, '2012-01-04', 0 union select 5, '2012-01-05', 0 union select 6, '2012-01-06', 5 union select 7, '2012-01-07', 0 union select 8, '2012-01-08', 5 union select 9, '2012-01-09', 0 union select 10, '2012-01-10', 0 

Query:

 ;with cte(s_id, e_id, b_id) as ( select s.id, e.id, b.id from #tab s left join #tab e on dateadd(dd, 1, s.date) = e.date and e.allocation = 0 left join #tab b on dateadd(dd, -1, s.date) = b.date and b.allocation = 0 where s.allocation = 0 ) select ts.date as [start date], te.date as [end date], count(*) as [day count] from ( select c1.s_id as s, ( select min(s_id) from cte c2 where c2.e_id is null and c2.s_id >= c1.s_id ) as e from cte c1 where b_id is null ) t join #tab t1 on t1.id between ts and te and t1.allocation = 0 join #tab ts on ts.id = ts join #tab te on te.id = te group by ts, te, ts.date, te.date 

Live example in data.SE.

Answer 4

Use of this data:

 CREATE TABLE MyTable (ID INT, Date DATETIME, Allocation INT); INSERT INTO MyTable VALUES (1, {d '2012-01-01'}, 0); INSERT INTO MyTable VALUES (2, {d '2012-01-02'}, 2); INSERT INTO MyTable VALUES (3, {d '2012-01-03'}, 0); INSERT INTO MyTable VALUES (4, {d '2012-01-04'}, 0); INSERT INTO MyTable VALUES (5, {d '2012-01-05'}, 0); INSERT INTO MyTable VALUES (6, {d '2012-01-06'}, 5); GO 

Try the following:

 WITH DateGroups (ID, Date, Allocation, SeedID) AS ( SELECT MyTable.ID, MyTable.Date, MyTable.Allocation, MyTable.ID FROM MyTable LEFT JOIN MyTable Prev ON Prev.Date = DATEADD(d, -1, MyTable.Date) AND Prev.Allocation = 0 WHERE Prev.ID IS NULL AND MyTable.Allocation = 0 UNION ALL SELECT MyTable.ID, MyTable.Date, MyTable.Allocation, DateGroups.SeedID FROM MyTable JOIN DateGroups ON MyTable.Date = DATEADD(d, 1, DateGroups.Date) WHERE MyTable.Allocation = 0 ), StartDates (ID, StartDate, DayCount) AS ( SELECT SeedID, MIN(Date), COUNT(ID) FROM DateGroups GROUP BY SeedID ), EndDates (ID, EndDate) AS ( SELECT SeedID, MAX(Date) FROM DateGroups GROUP BY SeedID ) SELECT StartDates.StartDate, EndDates.EndDate, StartDates.DayCount FROM StartDates JOIN EndDates ON StartDates.ID = EndDates.ID; 

The first section of the query is a recursive SELECT that binds to all rows that are alloc = 0 and whose previous day either does not exist or has a distribution! = 0. This effectively returns identifiers: 1 and 3 which are the starting dates of time periods, which you want to return to.

The recursive part of the same query starts with anchor strings and finds all subsequent dates that also have a distribution of = 0. SeedID tracks the bound identifier through all iterations.

The result so far is as follows:

 ID Date Allocation SeedID ----------- ----------------------- ----------- ----------- 1 2012-01-01 00:00:00.000 0 1 3 2012-01-03 00:00:00.000 0 3 4 2012-01-04 00:00:00.000 0 3 5 2012-01-05 00:00:00.000 0 3 

The following sub-query uses a simple GROUP BY to filter all start dates of each SeedID, as well as count days.

The last additional request does the same with the end dates, but this time the day count is not needed, since we already have this.

The final SELECT query combines the two together to combine the start and end dates and returns them along with the number of days.

Answer 5

Try it if it works for you. Here the SDATE for your DATE remains the same as your table.

 SELECT SDATE, CASE WHEN (SELECT COUNT(*)-1 FROM TABLE1 WHERE ID BETWEEN TBL1.ID AND (SELECT MIN(ID) FROM TABLE1 WHERE ID > TBL1.ID AND ALLOCATION!=0)) >0 THEN( CASE WHEN (SELECT SDATE FROM TABLE1 WHERE ID =(SELECT MAX(ID) FROM TABLE1 WHERE ID >TBL1.ID AND ID<(SELECT MIN(ID) FROM TABLE1 WHERE ID > TBL1.ID AND ALLOCATION!=0))) IS NULL THEN SDATE ELSE (SELECT SDATE FROM TABLE1 WHERE ID =(SELECT MAX(ID) FROM TABLE1 WHERE ID >TBL1.ID AND ID<(SELECT MIN(ID) FROM TABLE1 WHERE ID > TBL1.ID AND ALLOCATION!=0))) END )ELSE (SELECT SDATE FROM TABLE1 WHERE ID = (SELECT MAX(ID) FROM TABLE1 WHERE ID > TBL1.ID ))END AS EDATE ,CASE WHEN (SELECT COUNT(*)-1 FROM TABLE1 WHERE ID BETWEEN TBL1.ID AND (SELECT MIN(ID) FROM TABLE1 WHERE ID > TBL1.ID AND ALLOCATION!=0)) <0 THEN (SELECT COUNT(*) FROM TABLE1 WHERE ID BETWEEN TBL1.ID AND (SELECT MAX(ID) FROM TABLE1 WHERE ID > TBL1.ID )) ELSE (SELECT COUNT(*)-1 FROM TABLE1 WHERE ID BETWEEN TBL1.ID AND (SELECT MIN(ID) FROM TABLE1 WHERE ID > TBL1.ID AND ALLOCATION!=0)) END AS DAYCOUNT FROM TABLE1 TBL1 WHERE ALLOCATION = 0 AND (((SELECT ALLOCATION FROM TABLE1 WHERE ID=(SELECT MAX(ID) FROM TABLE1 WHERE ID < TBL1.ID))<> 0 ) OR (SELECT MAX(ID) FROM TABLE1 WHERE ID < TBL1.ID)IS NULL); 

Answer 6

Solution without CTE:

 SELECT a.aDate AS StartDate , MIN(c.aDate) AS EndDate , (datediff(day, a.aDate, MIN(c.aDate)) + 1) AS DayCount FROM ( SELECT x.aDate, x.allocation, COUNT(*) idn FROM table1 x JOIN table1 y ON y.aDate <= x.aDate GROUP BY x.id, x.aDate, x.allocation ) AS a LEFT JOIN ( SELECT x.aDate, x.allocation, COUNT(*) idn FROM table1 x JOIN table1 y ON y.aDate <= x.aDate GROUP BY x.id, x.aDate, x.allocation ) AS b ON a.idn = b.idn + 1 AND b.allocation = a.allocation LEFT JOIN ( SELECT x.aDate, x.allocation, COUNT(*) idn FROM table1 x JOIN table1 y ON y.aDate <= x.aDate GROUP BY x.id, x.aDate, x.allocation ) AS c ON a.idn <= c.idn AND c.allocation = a.allocation LEFT JOIN ( SELECT x.aDate, x.allocation, COUNT(*) idn FROM table1 x JOIN table1 y ON y.aDate <= x.aDate GROUP BY x.id, x.aDate, x.allocation ) AS d ON c.idn = d.idn - 1 AND d.allocation = c.allocation WHERE b.idn IS NULL AND c.idn IS NOT NULL AND d.idn IS NULL AND a.allocation = 0 GROUP BY a.aDate 

Example