find start and stop date for contiguous dates in multiple rows

Question

I'm having a bit of trouble displaying correct data from my table. Im not really sure what to search for either. Im not sure min(column) or max(column) will help me here. Lets see if i can explain my problem.

My table contains this data:

> Code (nvarchar) | DateFrom (datetime) | DateTo (datetime)
> ========================================================= 
> 3006            | 2014-06-18 07:00:00 | 2014-06-18 08:00:00 
> 3006            | 2014-06-18 09:00:00 | 2014-06-18 22:00:00 
> 1006            | 2014-06-18 07:00:00 | 2014-06-18 09:00:00
> 1006            | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 
> 1006            | 2014-06-18 08:10:00 | 2014-06-18 18:00:00

I am going to present this by a view. It will be grouped by Code.

What i want is this output:

> Code | DateFrom            | DateTo
> =========================================================
> 3006 | 2014-06-08 07:00:00 | 2014-06-18 08:00:00
> 3006 | 2014-06-18 09:00:00 | 2014-06-18 22:00:00
> 1006 | 2014-06-18 07:00:00 | 2014-06-18 18:00:00

As you see if there are gaps between DateTo and DateFrom i want it to be presented as two rows. But if the next "DateFrom" with the same code begins before (or at same time) as DateTo ends, i want that "DateTo" to be shown instead.

I don't see how i could use the function max() or min() in this case. Because of the gaps that can be during the timeslots.

Do you guys have any clue?

Im using MS SQL 2012

Thanks in advance!

edit: as commented. Islands could be my solloution?

No, i havent. I'm not familiar with that function. I will google it. :) — Andreas, Jun 18 '14 at 15:55
I dont think that is what i need. That will catch the next dateto even if there are gaps between dateto and datefrom. — Andreas, Jun 18 '14 at 16:04
I think what you want is called the `gaps-and-islands` technique to identify runs/gaps in data. — Dave C, Jun 18 '14 at 18:56
That one might do the trick someway. I will have a look at that! — Andreas, Jun 18 '14 at 19:45

Serpiton · Accepted Answer · 2014-07-02T08:41:22.203

The old answer had a weakness: every row is checked only with the previous to verify if the period are overlapping, if an earlier row have a period that last more the logic will not consider it. For example:

Code | DateStart           | DateFrom            | Overlap
-----+---------------------+---------------------+---------
1006 | 2014-06-18 07:00:00 | 2014-06-18 19:00:00 | 0 
1006 | 2014-06-18 08:10:00 | 2014-06-18 10:00:00 | 1 
1006 | 2014-06-18 16:00:00 | 2014-06-18 20:30:00 | 0 <- don't overlap with 
                                                        previous but overlap
                                                        with the first

To improve that PrevStop need to become LastStop and have the value of the max of the previous DateFrom for the Code

With N AS (
  SELECT Code, DateFrom, DateTo
       , LastStop = MAX(DateTo) 
                    OVER (PARTITION BY Code ORDER BY DateFrom, DateTo 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
  FROM   Table1
), B AS (
  SELECT Code, DateFrom, DateTo
       , Block = SUM(CASE WHEN LastStop Is Null Then 1
                          WHEN LastStop < DateFrom Then 1
                          ELSE 0
                    END)
                 OVER (PARTITION BY Code ORDER BY DateFrom, LastStop)
  FROM   N
)
SELECT Code
     , MIN(DateFrom) DateFrom
     , MAX(DateTo) DateTo
FROM   B
GROUP BY Code, Block
ORDER BY Code, Block

SQLFiddle Demo

ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING is needed to remove the current row from theMAX.

Old Answer

This query works only every the period is not completely inside the precedent.

The idea is to check for every row if it is linked with the next/previous one.
If rows are linked they form a block and they will be grouped together to get the first DateFrom and the last DateTo

With N AS (
  SELECT Code, DateFrom, DateTo
       , PrevStop = LAG(DateTo, 1, NULL) 
                    OVER (PARTITION BY Code ORDER BY DateFrom)
  FROM   Table1
), B AS (
  SELECT Code, DateFrom, DateTo
       , Block = SUM(CASE WHEN PrevStop Is Null Then 1
                          WHEN PrevStop < DateFrom Then 1
                          ELSE 0
                     END)
                 OVER (PARTITION BY Code ORDER BY PrevStop)
  FROM   N
)
SELECT Code
     , MIN(DateFrom) DateFrom
     , MAX(DateTo) DateTo
FROM   B
GROUP BY Code, Block
ORDER BY Code, Block

SQLFiddle demo with some data added to check with more block on the same code/day

The query search for the block starter checking every row if they are the first for the code (PrevStop IS NULL) or if they are outside the previous one (PrevStop < DateFrom).

The windowed SUM retrieve only the previous row by the ORDER to create costant value for block of linked data, for example with the test data we will get

Code | DateStart           | DateFrom            | Starter | Block
-----+---------------------+---------------------+---------+------
1006 | 2014-06-18 07:00:00 | 2014-06-18 09:00:00 |       1 |     1
1006 | 2014-06-18 08:10:00 | 2014-06-18 06:00:00 |       0 |     1
1006 | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 |       0 |     1
1006 | 2014-06-18 07:00:00 | 2014-06-18 07:30:00 |       1 |     2
1006 | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 |       1 |     3
1006 | 2014-06-18 08:10:00 | 2014-06-18 09:00:00 |       0 |     3
3006 | 2014-06-18 07:00:00 | 2014-06-18 08:00:00 |       1 |     1
3006 | 2014-06-18 09:00:00 | 2014-06-18 10:00:00 |       1 |     2

grouping by Code and Block get the result

Seems like this does not make the trick. I get multiple rows with the same code, even if they are in the same timeslot. — Andreas, Jun 25 '14 at 08:32
Seems like the problem occur as there are more than 1 with the same starttime? http://www.sqlfiddle.com/#!6/a5c59/1 — Andreas, Jul 02 '14 at 06:54

find start and stop date for contiguous dates in multiple rows

1 Answers1

Linked