Continuous Date Ranges - Gaps and Islands

Question

CREATE TABLE T1
    (Asset_Id int, Trailer_Id int, AssignStart datetime, AssignEnd DATETIME)
;
INSERT INTO T1
    (Asset_Id, Trailer_Id, AssignStart, AssignEnd)
VALUES
    (37124, 32607, '2018-04-19 08:55:00', '2018-05-05 10:00:00.000'),
    (37124, 32607, '2018-05-05 11:23:00', NULL),
    (33000, 30000, '2018-04-01 15:00:00', '2018-04-15 10:30:00.000'),
    (34000, 31000, '2018-04-05 10:00:00', '2018-04-10 09:30:00.000'),
    (34000, 32500, '2018-04-10 09:31:00', NULL),
    (37000, 32600, '2018-04-19 08:55:00', '2018-04-25 08:30:00.000'),
    (37000, 32600, '2018-04-25 09:23:00', '2018-04-25 10:00:00.000'),
    (37000, 32600, '2018-04-25 11:23:00', '2018-04-30 15:00:00.000'),
    (37000, 32600, '2018-04-30 16:15:00', '2018-04-30 17:30:00.000'),
    (37000, 32600, '2018-05-01 18:23:00', NULL),
    (38000, 36000, '2018-05-01 10:00:00', '2018-05-10 06:30:00.000'),
    (38000, 36000, '2018-05-15 09:00:00', '2018-05-20 11:00:00.000'),
    (38000, 36000, '2018-05-20 12:00:00', NULL),
    (33000, 30000, '2018-05-01 10:00:00', NULL)
;

I have the following sample data -

Asset_Id    Trailer_Id  AssignStart             AssignEnd
37124       32607       2018-04-19 08:55:00.000 2018-05-05 10:00:00.000
37124       32607       2018-05-05 11:23:00.000 NULL
33000       30000       2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
34000       31000       2018-04-05 10:00:00.000 2018-04-10 09:30:00.000
34000       32500       2018-04-10 09:31:00.000 NULL
37000       32600       2018-04-19 08:55:00.000 2018-04-25 08:30:00.000
37000       32600       2018-04-25 09:23:00.000 2018-04-25 10:00:00.000
37000       32600       2018-04-25 11:23:00.000 2018-04-30 15:00:00.000
37000       32600       2018-04-30 16:15:00     2018-04-30 17:30:00.000
37000       32600       2018-05-01 18:23:00     NULL
38000       36000       2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000       36000       2018-05-15 09:00:00.000 2018-05-20 11:00:00.000
38000       36000       2018-05-20 12:00:00.000 NULL
33000       30000       2018-05-01 10:00:00.000 NULL

As you can see some of the assignments between Assets and Trailers are ended and started again on the same day - except for last row - Gaps and Islands

Example (1)

Asset_Id    Trailer_Id  AssignStart             AssignEnd
37000       32600       2018-04-19 08:55:00.000 2018-04-25 08:30:00.000
37000       32600       2018-04-25 09:23:00.000 2018-04-25 10:00:00.000
37000       32600       2018-04-25 11:23:00.000 2018-04-30 15:00:00.000
37000       32600       2018-04-30 16:15:00.000 2018-04-30 17:30:00.000
37000       32600       2018-05-01 18:23:00.000 NULL

Output for this sample I am expecting is

Asset_Id    Trailer_Id  AssignStart             AssignEnd
37000       32600       2018-04-19 08:55:00.000 2018-04-30 17:30:00.000
37000       32600       2018-05-01 18:23:00.000 NULL

Now this is another section with the Gaps and Islands. Some of assignments between the same assets and trailers are ended and then started again on a future date

Example (2)

Asset_Id    Trailer_Id  AssignStart             AssignEnd
33000       30000       2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
33000       30000       2018-05-01 10:00:00.000 NULL

Output for this sample I am expecting is

Asset_Id    Trailer_Id  AssignStart             AssignEnd
33000       30000       2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
33000       30000       2018-05-01 10:00:00.000 NULL

Example (3)

Asset_Id    Trailer_Id  AssignStart             AssignEnd
38000       36000       2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000       36000       2018-05-15 09:00:00.000 2018-05-20 11:00:00.000
38000       36000       2018-05-20 12:00:00.000 NULL

Output for this sample I am expecting is

Asset_Id    Trailer_Id  AssignStart             AssignEnd
38000       36000       2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000       36000       2018-05-15 09:00:00.000 NULL

I struggling to write a query that will provide the following output

Asset_Id    Trailer_Id  AssignStart             AssignEnd
37124       32607       2018-04-19 08:55:00.000 NULL
33000       30000       2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
34000       31000       2018-04-05 10:00:00.000 2018-04-10 09:30:00.000
34000       32500       2018-04-10 09:31:00.000 NULL
37000       32600       2018-04-19 08:55:00.000 2018-04-30 17:30:00.000
37000       32600       2018-05-01 18:23:00.000 NULL
38000       36000       2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000       36000       2018-05-15 09:00:00.000 NULL
33000       30000       2018-05-01 10:00:00.000 NULL

Using the answer from this SO answer as a base this is my attempt, it's not quite right

Sabin B · Accepted Answer · 2018-06-14 15:57:48Z

You can use the recursive approach , with the help of a CTE. For this , I added a Row_Number so , to be sure that it's the next record verified.

Then creating the recursive CTE...named cte. Here it's important the field rn as rn_init - this is the base when it's creating the chain of records.

;
WITH fr AS -- first records
(
SELECT [Asset_Id], [Trailer_Id], [AssignStart], [AssignEnd]
  ,ROW_NUMBER()OVER( PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY 
   [AssignStart], [AssignEnd]) AS rn
FROM  T1
)
,cte AS
(
 SELECT [Asset_Id], [Trailer_Id]
      , [AssignStart] as [AssignStart_init] , [AssignEnd] as [AssignEnd_Init] 
      , [AssignStart] as [AssignStart],  [AssignEnd] as [AssignEnd]
      , 0 as lvl
      , rn as rn
      , rn as rn_init
 FROM fr
 --WHERE [AssignEnd] is not null

 UNION ALL

 SELECT 
     T1.[Asset_Id], T1.[Trailer_Id]
     ,C.[AssignStart_init],C.[AssignEnd_Init]
     ,T1.[AssignStart], T1.[AssignEnd]
     ,C.lvl + 1 
     ,T1.rn
     ,C.rn_init
 FROM fr AS T1
     INNER JOIN cte as C
        ON T1.[Asset_Id] = C.[Asset_Id]
        AND T1.[Trailer_Id] = C.[Trailer_Id]
        AND CONVERT(DATE,C.[AssignEnd]) = CONVERT(DATE,T1.[AssignStart])
        AND C.[AssignEnd] <= T1.[AssignStart]
        AND C.rn + 1 = T1.rn
)

Here is dealing with NULL case for the AssignEnd and also important , to count the 'chain of records' count(rn_init) as c_rn_init. Base on this, it's later filtered.

--select * from cte
,myPrecious AS
(
select  --C.* 
   C.[Asset_Id],c.[Trailer_Id]
    ,MIN([AssignStart]) as AssignStart
    --,MAX([AssignEnd]) as AssignEnd
    ,CASE WHEN MAX(isnull([AssignEnd],[AssignStart])) = MAX([AssignStart]) THEN NULL ELSE MAX(isnull([AssignEnd],[AssignStart])) END as [AssignEnd]
    ,count(rn_init) as c_rn_init
 from cte as c
group by C.[Asset_Id],c.[Trailer_Id], C.rn_init

 )

This if the final query.Added Row_number , so to filter out records , ORDER BY c_rn_init DESC

 SELECT 
  Asset_Id,Trailer_Id,AssignStart,AssignEnd
  --,c_rn_init
  --,rn
  FROM(
          SELECT * 
               ,row_number() OVER(PARTITION BY [Asset_Id],[Trailer_Id] , AssignEnd ORDER BY c_rn_init DESC) as rn
          FROM myPrecious
      )A
 WHERE A.rn = 1
 order by [Asset_Id],[Trailer_Id]  ,[AssignStart],[AssignEnd]

The output:

Asset_Id    Trailer_Id  AssignStart         AssignEnd
33000       30000       01/04/2018 15:00:00 15/04/2018 10:30:00
33000       30000       01/05/2018 10:00:00 null
34000       31000       05/04/2018 10:00:00 10/04/2018 09:30:00
34000       32500       10/04/2018 09:31:00 null
37000       32600       19/04/2018 08:55:00 30/04/2018 17:30:00
37000       32600       01/05/2018 18:23:00 null
37124       32607       19/04/2018 08:55:00 null
38000       36000       01/05/2018 10:00:00 10/05/2018 06:30:00
38000       36000       15/05/2018 09:00:00 null

dbfiddle

Sabin B · Accepted Answer · 2018-06-14 09:42:37Z

My idea is to get the AssignStart value, from the next record and do a difference in day between [AssignEnd] (of the current row) and AssignStart (of the next row) (Step1)

if it's sql server 2012 ,we can use LEAD

LEAD([AssignStart],1,[AssignStart]) OVER(PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY [AssignStart]) as next_AssignStart

if it's 2008, we can not use LEAD , so use OUTER APPLY to get the record:

 OUTER APPLY
   (SELECT TOP(1) T2.[AssignStart]
      FROM T1 as T2
      WHERE T1.[Asset_Id] = T2.[Asset_Id]
          AND T1.[Trailer_Id] = T2.[Trailer_Id] 
          AND T1.[AssignStart]<T2.[AssignStart] 
      ORDER BY T2.[AssignStart] ASC 
   )OA

this will give me the AssignStart of the next row , base on [Asset_Id], [Trailer_Id]

Step2 : do that difference column : DATEDIFF(DAY,[AssignEnd],next_AssignStart)

SELECT 
  [Asset_Id], [Trailer_Id], [AssignStart], [AssignEnd]
  ,next_AssignStart
  ,IsNUll(DATEDIFF(DAY,[AssignEnd],next_AssignStart),0) as diff_days
FROM
(
SELECT
    [Asset_Id], [Trailer_Id],T1.[AssignStart], [AssignEnd]
    --,LEAD([AssignStart],1,[AssignStart]) OVER(PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY [AssignStart]) as next_AssignStart
    ,OA.[AssignStart] as next_AssignStart
FROM
   T1
   OUTER APPLY
   (SELECT TOP(1) T2.[AssignStart]
      FROM T1 as T2
      WHERE T1.[Asset_Id] = T2.[Asset_Id]
          AND T1.[Trailer_Id] = T2.[Trailer_Id] 
          AND T1.[AssignStart]<T2.[AssignStart] 
      ORDER BY T2.[AssignStart] ASC 
   )OA
)A
ORDER BY [Asset_Id], [Trailer_Id], [AssignStart], [AssignEnd]

Step3: do a group by on this column, from step2

SELECT 
  [Asset_Id], [Trailer_Id]
  ,MIN([AssignStart]) as [AssignStart]
  ,CASE WHEN MAX(isnull([AssignEnd],[AssignStart])) = MAX([AssignStart]) THEN NULL ELSE MAX(isnull([AssignEnd],[AssignStart])) END as [AssignEnd]
FROM
(
SELECT
    [Asset_Id], [Trailer_Id], T1.[AssignStart], [AssignEnd]
    --,LEAD([AssignStart],1,[AssignStart]) OVER(PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY [AssignStart]) as next_AssignStart
    ,OA.[AssignStart] as next_AssignStart
FROM
   T1
   OUTER APPLY
   (SELECT TOP(1) T2.[AssignStart]
      FROM T1 as T2
      WHERE T1.[Asset_Id] = T2.[Asset_Id]
          AND T1.[Trailer_Id] = T2.[Trailer_Id] 
          AND T1.[AssignStart]<T2.[AssignStart] 
      ORDER BY T2.[AssignStart] ASC 
   )OA
)A
GROUP BY [Asset_Id], [Trailer_Id], IsNUll(DATEDIFF(DAY,[AssignEnd],next_AssignStart),0)
ORDER BY [Asset_Id], [Trailer_Id],[AssignStart]

output:

Asset_Id    Trailer_Id  AssignStart         AssignEnd
33000       30000       01/04/2018 15:00:00 15/04/2018 10:30:00
33000       30000       01/05/2018 10:00:00 null
34000       31000       05/04/2018 10:00:00 10/04/2018 09:30:00
34000       32500       10/04/2018 09:31:00 null
37000       32600       19/04/2018 08:55:00 null
37124       32607       19/04/2018 08:55:00 null
38000       36000       01/05/2018 10:00:00 10/05/2018 06:30:00
38000       36000       15/05/2018 09:00:00 null

ps: added Order by, so I can verify much easier.

ps2: because ,NULL it's ignored in MAX, I used this expression to handle the upper part of the interval

dbfiddle

Hit a bug. If I change this row in the sample data 37000 32600 2018-04-30 18:23:00.000 NULL to the following day 37000 32600 2018-05-01 18:23:00.000 NULL - the results are not as expected — Mazhar, Jun 14, 2018 at 11:29
Expected results for the change is: 37000 32600 2018-04-19 08:55:00 2018-04-30 17:30:00.000 37000 32600 2018-05-01 18:23:00.000 NULL — Mazhar, Jun 14, 2018 at 11:50

user179304user179304 · Accepted Answer · 2019-04-29 17:07:42Z

I present my solution for T-SQL.

Fiddle SQL Server 2017

The problem:

We need to pack records by overlapping date range (base time unit 'day') partitioned by Asset and Trailer.

The solution:

Packing overlapping date ranges requires 3 steps:

Step 1: determine a GroupStart

    select Asset_Id, Trailer_Id, AssignStart, AssignEnd
                    , case when max(AssignEnd) 
                        over (partition by Asset_Id, Trailer_Id order by AssignEnd, AssignStart rows between unbounded preceding and 1 preceding)
                            >= AssignStart
                        then 0 else 1 end GroupStart
                from  (
                    select Asset_Id, Trailer_Id, 
                           convert(date, AssignStart) AssignStart, 
                           convert(date, coalesce(AssignEnd, '99991231')) AssignEnd
                    from T1
            ) t0

Step 2: determine the Groups

select Asset_Id, Trailer_Id, AssignStart, AssignEnd, sum(GroupStart) over (partition by Asset_Id, Trailer_Id order by AssignStart) Grp 
        from (...) t0
) p0

Step 3: determine the packed date range

select Asset_Id, Trailer_Id, min(AssignStart) AssignStart, max(AssignEnd) AssignEnd 
    from (...) p1
    group by Asset_Id, Trailer_Id, Grp

Setup:

CREATE TABLE T1
    (Asset_Id int, Trailer_Id int, AssignStart datetime, AssignEnd DATETIME)
;
INSERT INTO T1
    (Asset_Id, Trailer_Id, AssignStart, AssignEnd)
VALUES
    (37124, 32607, '2018-04-19 08:55:00', '2018-05-05 10:00:00.000'),
    (37124, 32607, '2018-05-05 11:23:00', NULL),
    (33000, 30000, '2018-04-01 15:00:00', '2018-04-15 10:30:00.000'),
    (34000, 31000, '2018-04-05 10:00:00', '2018-04-10 09:30:00.000'),
    (34000, 32500, '2018-04-10 09:31:00', NULL),
    (37000, 32600, '2018-04-19 08:55:00', '2018-04-25 08:30:00.000'),
    (37000, 32600, '2018-04-25 09:23:00', '2018-04-25 10:00:00.000'),
    (37000, 32600, '2018-04-25 11:23:00', '2018-04-30 15:00:00.000'),
    (37000, 32600, '2018-04-30 16:15:00', '2018-04-30 17:30:00.000'),
    (37000, 32600, '2018-05-01 18:23:00', NULL),
    (38000, 36000, '2018-05-01 10:00:00', '2018-05-10 06:30:00.000'),
    (38000, 36000, '2018-05-15 09:00:00', '2018-05-20 11:00:00.000'),
    (38000, 36000, '2018-05-20 12:00:00', NULL),
    (33000, 30000, '2018-05-01 10:00:00', NULL)

Full Query:

 with packed as (
    select Asset_Id, Trailer_Id, min(AssignStart) AssignStart, max(AssignEnd) AssignEnd from (
        select Asset_Id, Trailer_Id, AssignStart, AssignEnd, sum(GroupStart) over (partition by Asset_Id, Trailer_Id order by AssignStart) Grp from (
            select Asset_Id, Trailer_Id, AssignStart, AssignEnd
                , case when max(AssignEnd) 
                    over (partition by Asset_Id, Trailer_Id order by AssignEnd, AssignStart rows between unbounded preceding and 1 preceding)
                        >= AssignStart
                    then 0 else 1 end GroupStart
            from (
                    select Asset_Id, Trailer_Id, 
                           convert(date, AssignStart) AssignStart, 
                           convert(date, coalesce(AssignEnd, '99991231')) AssignEnd
                    from T1
            ) t0
        ) p0
    ) p1
    group by Asset_Id, Trailer_Id, Grp
)
select Asset_Id, Trailer_Id, AssignStart, nullif(AssignEnd, '99991231')
from packed
order by Asset_Id, Trailer_Id, AssignStart;

pacreely · Accepted Answer · 2018-06-14 11:31:24Z

You can try the self-join as an alternative.

In my environment (with the sample data) it is generating less reads than the OUTER APPLY. and producing a less costly execution plan. But when I add different Indexes the OUTER APPLY sometimes comes out on top.

--SET STATISTICS IO ON
--SET STATISTICS TIME ON

;WITH cteSequence AS
(
SELECT 
    CUR.Asset_Id 
    ,CUR.Trailer_Id
    ,CUR.AssignStart
    ,CUR.AssignEnd
    ,ROW_NUMBER() OVER(PARTITION BY Asset_Id,Trailer_Id ORDER BY AssignStart) AS SEQ
FROM 
    T1 CUR
)
SELECT 
    CUR.Asset_Id 
    ,CUR.Trailer_Id
    ,MIN(CUR.AssignStart) AS AssignStart
    ,CASE 
        WHEN MAX(COALESCE(NXT.[AssignEnd],CUR.[AssignStart])) = MAX(NXT.[AssignStart]) THEN NULL 
        ELSE MAX(COALESCE(CUR.[AssignEnd],NXT.[AssignStart])) END as [AssignEnd]
FROM 
    cteSequence CUR
        LEFT JOIN cteSequence NXT
            ON
            CUR.Asset_Id = NXT.Asset_Id
            AND
            CUR.Trailer_Id = NXT.Trailer_Id
            AND
            CUR.SEQ = NXT.SEQ-1
GROUP BY 
    CUR.Asset_Id 
    ,CUR.Trailer_Id
    ,COALESCE(DATEDIFF(DD,CUR.AssignEnd,NXT.AssignStart),0)

Thanks for this however, this suffers from the same bug as sabin's answer. If I change this row in the sample data 37000 32600 2018-04-30 18:23:00.000 NULL to the following day 37000 32600 2018-05-01 18:23:00.000 NULL - the results are not as expected. Expected results for the change is: 37000 32600 2018-04-19 08:55:00 2018-04-30 17:30:00.000 37000 32600 2018-05-01 18:23:00.000 NULL — Mazhar, Jun 14, 2018 at 11:56

Stack Exchange Network

Continuous Date Ranges - Gaps and Islands

4 Answers 4

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008-r2
gaps-and-islands
or ask your own question.

Hot Network Questions

Continuous Date Ranges - Gaps and Islands

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2008-r2gaps-and-islands or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2008-r2
gaps-and-islands
or ask your own question.