Geek Answers Handbook

Receive a sequence of days from days

We have a table of days like:

ID Date 1 2015-07-29 2 2015-07-30 3 2015-07-31 4 2015-08-01 5 2015-08-03 7 2015-08-04 8 2015-08-05 9 2015-08-06 10 2015-08-07 11 2015-08-10

And we want to find all the sequences (day + 1). The result should be something like this:

 Start End 2015-07-29 2015-08-01 2015-08-03 2015-08-07 2015-08-10 2015-08-10

1. Update

First, I changed the answer of Divanche of Calra to use RowNumber instead of Id (Id is auto-increment, so it’s possible that identifiers are missing)

 DECLARE @P TABLE(DATE DATE) INSERT INTO @P SELECT MIN([DATE]) FROM MietvertragsArtikelDays UNION SELECT T1.[DATE] FROM (SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) RowNumber, [DATE] FROM MietvertragsArtikelDays) AS T1 INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) RowNumber, [DATE] FROM MietvertragsArtikelDays) AS T2 ON T1.RowNumber=T2.RowNumber+1 WHERE DATEDIFF(DAY,T2.[DATE],T1.[DATE]) <>1 UNION SELECT T2.[DATE] FROM (SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) RowNumber, [DATE] FROM MietvertragsArtikelDays) AS T1 INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) RowNumber, [DATE] FROM MietvertragsArtikelDays) AS T2 ON T1.RowNumber=T2.RowNumber+1 WHERE DATEDIFF(DAY,T2.[DATE],T1.[DATE]) <>1 DECLARE @X TABLE(DATE DATE, RN INT) INSERT INTO @X SELECT *, ROW_NUMBER() OVER(ORDER BY [DATE]) AS X FROM @P SELECT A.[DATE] Start, ISNULL(B.[DATE],A.[DATE]) [End] FROM @XA LEFT JOIN (SELECT [DATE], RN-1 AS RN FROM @X) B ON A.RN=B.RN WHERE A.RN%2=1

2. Update

The most elegant solution - Ughais

 ;WITH CTE as ( SELECT *,DATEDIFF(D,0,[Date]) - ROW_NUMBER()OVER(ORDER BY ID ASC) grp FROM MietvertragsArtikelDays ) SELECT MIN([Date]),MAX([Date]) FROM CTE GROUP BY grp
+4
source share
3 answers

This is the problem of the Island and the gap . You can use ROW_NUMBER and DATEDIFF . Something like that.

SQL Fiddle

Data examples

 DECLARE @Dates TABLE ([ID] int, [Date] datetime); INSERT INTO @Dates ([ID], [Date]) VALUES (1, '2015-07-29 00:00:00'), (2, '2015-07-30 00:00:00'), (3, '2015-07-31 00:00:00'), (4, '2015-08-01 00:00:00'), (5, '2015-08-03 00:00:00'), (7, '2015-08-04 00:00:00'), (8, '2015-08-05 00:00:00'), (9, '2015-08-06 00:00:00'), (10, '2015-08-07 00:00:00'), (11, '2015-08-10 00:00:00');

Query

 ;WITH CTE as ( SELECT *,DATEDIFF(D,0,[Date]) - ROW_NUMBER()OVER(ORDER BY ID ASC) grp FROM @Dates ) SELECT MIN([Date]),MAX([Date]) FROM CTE GROUP BY grp

Exit

 2015-07-29 00:00:00.000 2015-08-01 00:00:00.000 2015-08-03 00:00:00.000 2015-08-07 00:00:00.000 2015-08-10 00:00:00.000 2015-08-10 00:00:00.000
+4
source

As I started with an answer that has already been posted, maybe I made it very complicated. But it works.

 DECLARE @T TABLE(ID INT, DATE DATE) INSERT INTO @T SELECT 1, '2015-07-29' UNION ALL SELECT 2, '2015-07-30' UNION ALL SELECT 3, '2015-07-31' UNION ALL SELECT 4, '2015-08-01' UNION ALL SELECT 5, '2015-08-03' UNION ALL SELECT 7, '2015-08-04' UNION ALL SELECT 8, '2015-08-05' UNION ALL SELECT 9, '2015-08-06' UNION ALL SELECT 10, '2015-08-07' UNION ALL SELECT 11, '2015-08-10' DECLARE @P TABLE(DATE DATE) INSERT INTO @P SELECT MIN([DATE]) FROM @T UNION SELECT T1.[DATE] FROM @T AS T1 INNER JOIN @T AS T2 ON T1.ID=T2.ID+1 WHERE DATEDIFF(DAY,T2.[DATE],T1.[DATE]) <>1 UNION SELECT T2.[DATE] FROM @T AS T1 INNER JOIN @T AS T2 ON T1.ID=T2.ID+1 WHERE DATEDIFF(DAY,T2.[DATE],T1.[DATE]) <>1 DECLARE @X TABLE(DATE DATE, RN INT) INSERT INTO @X SELECT *, ROW_NUMBER() OVER(ORDER BY [DATE]) AS X FROM @P SELECT A.[DATE], B.[DATE] FROM @XA LEFT JOIN (SELECT [DATE], RN-1 AS RN FROM @X) B ON A.RN=B.RN WHERE A.RN%2=1

Please excuse the standards and all. Will edit later that day. Sorry for that.

+1
source

This will work regardless of the order and value of Id:

 Declare @dates table(ID int, D datetime) Insert Into @dates(ID, D) values (1, '2015-07-29') , (2, '2015-07-30') , (3, '2015-07-31') , (4, '2015-08-01') , (5, '2015-08-03') , (7, '2015-08-04') , (8, '2015-08-05') , (9, '2015-08-06') , (10, '2015-08-07') , (11, '2015-08-10') ; With start(ID, D) as ( -- Get 1st Dates Select d1.ID, d1.D From @dates as d1 Left Join @dates as d2 On d1.D = DATEADD(DAY, 1, d2.D) Where d2.ID is NULL ), loop(startD, endD) as ( -- Loop through consecutives dates Select D, D From start Union All Select l.startD, sD From loop as l Inner Join @dates as s On sD = DATEADD(DAY, 1, l.endD) ) -- Get max end date for each start date Select startD as [Start], max(endD) as [End] From loop group by startD

Output:

 Start End 2015-07-29 2015-08-01 2015-08-03 2015-08-07 2015-08-10 2015-08-10
0
source

More articles:


All Articles