SQL Split Island On Criteria

Question

I have a SQL table with From and To dates like so:

Row    From                    To 
--------------------------------------------------
1      2017-10-28 00:00:00     2017-10-30 00:00:00
2      2017-10-30 00:00:00     2017-10-31 00:00:00
3      2017-10-31 00:00:00     2017-10-31 07:30:00
4      2017-10-31 14:41:00     2017-10-31 15:14:00
5      2017-10-31 17:13:00     2017-11-01 00:00:00
6      2017-11-01 00:00:00     2017-11-01 23:45:00
7      2017-11-02 03:13:00     2017-11-02 07:56:00

I need to group consecutive data into islands. The data is non-overlapping. This is done easily enough using this query:

;with Islands as
(
    SELECT
        min([From]) as [From]
        ,max([To]) as [To]
    FROM
    (
        select
            [From],
            [To],
            sum(startGroup) over (order by [From]) StartGroup
        from 
        (       
            SELECT 
                [From],
                [To],
                (case when [From] <= lag([To]) over (order by [From])
                                then 0
                                else 1
                        end) as StartGroup
                FROM dbo.DateTable
        ) IsNewIsland
    ) GroupedIsland
    group by StartGroup
)
select *
from Islands

And gives me these results:

From                    To                 Rows 
-----------------------------------------------------
2017-10-28 00:00:00     2017-10-31 07:30:00      1-3
2017-10-31 14:41:00     2017-10-31 15:14:00      4
2017-10-31 17:13:00     2017-11-01 23:45:00      5-6
2017-11-02 03:13:00     2017-11-02 07:56:00      7

The problem I have is that I need to modify the query to cap/split the islands once they have gotten enough records to be a certain total duration. This is an input/hardcoded value. The split includes the entire record, not splitting in the middle of a record's From-To range. As an example, I need to split islands to be 27 hours. This would give this result:

From                    To                       Rows 
-----------------------------------------------------
2017-10-29 00:00:00     2017-10-30 00:00:00      1
2017-10-30 00:00:00     2017-10-31 07:30:00      2-3
2017-10-31 17:13:00     2017-11-01 23:45:00      5-6

The first island was split because rows 1 and 2 alone created a 27 hour period. Rows 4 and 7 are not enough to create an island, so they are ignored.

I tried pulling this information via a lag function in the inner select to compute the "rolling duration" across rows, but it would not work on islands that spanned more than 2 rows because it would only track the last row's duration and I could not "carry" the calculation forward.

SELECT 
  [From],
  [To],
  (case when [From] <= lag([To]) over (order by [From]
                            then (datediff(minute, [From], [To]) + lag(datediff(minute, [From], [To])) over (order by [From]))
                            else datediff(minute, [From], [To])
                    end) as RollingDuration,
  (case when [From] <= lag([To]) over (order by [From])
                      then 0
                      else 1
          end) as StartGroup
FROM dbo.DateTable

Answer 1

The "least worst" way I can think of doing it is a "quirky update". (Google it, I honestly didn't make it up.)

• http://www.sqlservercentral.com/articles/T-SQL/68467/
  • Copy the data in to a new table with one or more additional (blank) fields
  • Use a CLUSTERED PRIMARY KEY to ensure the rows are updated in correct sequence
  • Use UPDATE and user variables to iterate through rows and store results of calculations

Using that I can start a new group if there is a gap, or a running total reaches 27 hours. Then proceed as usual.

-- New table to work through
----------------------------------------------------------------------
-- Addition [group_start] field (identifies groups, and useful data)
-- PRIMARY KEY CLUSTERED to enforce the order rows will be processed
----------------------------------------------------------------------

CREATE TABLE sample (
    id             INT,
    start          DATETIME,
    cease          DATETIME,
    group_start    DATETIME   DEFAULT(0),
    PRIMARY KEY CLUSTERED (group_start, start)   -- To force the order we will iterate the rows, and is useful in last step
);

INSERT INTO
    sample (
        id,
        start,
        cease
    )
VALUES
    (1,      '2017-10-28 00:00:00',     '2017-10-30 00:00:00'),
    (2,      '2017-10-30 00:00:00',     '2017-10-31 00:00:00'),
    (3,      '2017-10-31 00:00:00',     '2017-10-31 07:30:00'),
    (4,      '2017-10-31 14:41:00',     '2017-10-31 15:14:00'),
    (5,      '2017-10-31 17:13:00',     '2017-11-01 00:00:00'),
    (6,      '2017-11-01 00:00:00',     '2017-11-01 23:45:00'),
    (7,      '2017-11-02 03:13:00',     '2017-11-02 07:56:00')
;


-- Quirky Update
----------------------------------------------------------------------
-- Update [group_start] to the start of the current group
-- -> new group if gap since previous row
-- -> new group if previous row took group to 27 hours
-- -> else same group as previous row
----------------------------------------------------------------------

DECLARE @grp_start DATETIME = 0;

WITH
    lagged AS
(
    SELECT *, LAG(cease) OVER (ORDER BY group_start, start) AS lag_cease FROM sample
)
UPDATE
    lagged
SET
    @grp_start
        = group_start
            = CASE WHEN start <> lag_cease                     THEN start
                   WHEN start >= DATEADD(hour, 27, @grp_start) THEN start
                                                               ELSE @grp_start END
OPTION
    (MAXDOP 1)
;

-- Standard SQL to apply other logic
----------------------------------------------------------------------
-- MAX() OVER () to find end time of each group
-- WHERE to filter out any groups under 12 hours long
----------------------------------------------------------------------

SELECT
    *
FROM
(
    SELECT
        *,
        MAX(cease) OVER (PARTITION BY group_start)    AS group_cease
    FROM
        sample
)
   bounded_groups
WHERE
   group_cease >= DATEADD(hour, 12, group_start)
;

http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1bec5b3fe920c1affd58f23a11e280a0