MSSQL 2008 Merge Contiguous Dates With Groupings

Question

I have searched high and low for weeks now trying to find a solution to my problem.

As far as I can ascertain, my SQL Server version (2008r2) is a limiting factor on this but, I am positive there is a solution out there.

My problem is as follows:

A have a table with potential contiguous dates in the form of Customer-Status-DateStart-DateEnd-EventID.

I need to merge contiguous dates by customer and status - the status field can shift up and down throughout a customers pathway.

Some example data is as follows:

DECLARE @Tbl TABLE([CustomerID] INT
              ,[Status] INT
              ,[DateStart] DATE
              ,[DateEnd] DATE
              ,[EventID] INT)                 

INSERT INTO @Tbl
VALUES (1,1,'20160101','20160104',1)
      ,(1,1,'20160104','20160108',3)
      ,(1,2,'20160108','20160110',4)
      ,(1,1,'20160110','20160113',7)
      ,(1,3,'20160113','20160113',9)
      ,(1,3,'20160113',NULL,10)
      ,(2,1,'20160101',NULL,2)
      ,(3,2,'20160109','20160110',5)
      ,(3,1,'20160110','20160112',6)
      ,(3,1,'20160112','20160114',8)

Desired output:

Customer | Status | DateStart | DateEnd
---------+--------+-----------+-----------
1        |  1     | 2016-01-01| 2016-01-08
1        |  2     | 2016-01-08| 2016-01-10
1        |  1     | 2016-01-10| 2016-01-13
1        |  3     | 2016-01-13| NULL
2        |  1     | 2016-01-01| NULL
3        |  2     | 2016-01-09| 2016-01-10
3        |  1     | 2016-01-10| 2016-01-14

Any ideas / code will be greatly received.

Thanks,

Dan

Answer 1

Try this

    DECLARE @Tbl TABLE([CusomerID] INT
              ,[Status] INT
              ,[DateStart] DATE
              ,[DateEnd] DATE
              ,[EventID] INT)                 

INSERT INTO @Tbl
VALUES (1,1,'20160101','20160104',1)
      ,(1,1,'20160104','20160108',3)
      ,(1,2,'20160108','20160110',4)
      ,(1,1,'20160110','20160113',7)
      ,(1,3,'20160113','20160113',9)
      ,(1,3,'20160113',NULL,10)
      ,(2,1,'20160101',NULL,2)
      ,(3,2,'20160109','20160110',5)
      ,(3,1,'20160110','20160112',6)
      ,(3,1,'20160112','20160114',8)



;WITH CTE
AS
(
  SELECT CusomerID ,
         Status ,
         DateStart ,
         COALESCE(DateEnd, '9999-01-01') AS DateEnd,
         EventID, 
         ROW_NUMBER() OVER (ORDER BY CusomerID, EventID) RowId,  
         ROW_NUMBER() OVER (PARTITION BY CusomerID, Status ORDER BY EventID) StatusRowId FROM @Tbl
)

SELECT
    A.CusomerID ,
    A.Status ,
    A.DateStart ,
    CASE WHEN A.DateEnd = '9999-01-01' THEN NULL
    ELSE A.DateEnd END AS DateEnd
FROM
(
    SELECT 
        CTE.CusomerID, 
        CTE.Status,
        MIN(CTE.DateStart) AS DateStart,
        MAX(CTE.DateEnd) AS DateEnd
    FROM 
        CTE
    GROUP BY 
        CTE.CusomerID, 
        CTE.Status,
        CTE.StatusRowId -CTE.RowId      
) A
ORDER BY A.CusomerID, A.DateStart

Output

CusomerID   Status      DateStart  DateEnd
----------- ----------- ---------- ----------
1           1           2016-01-01 2016-01-08
1           2           2016-01-08 2016-01-10
1           1           2016-01-10 2016-01-13
1           3           2016-01-13 NULL
2           1           2016-01-01 NULL
3           2           2016-01-09 2016-01-10
3           1           2016-01-10 2016-01-14