4
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

4 Answers 4

3

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

0
1

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

3
  • 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
  • And in this case what will be the output ?
    – Sabin B
    Jun 14, 2018 at 11:43
  • 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
1

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;
0

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)
1
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.