Cumulative time difference per group

Question

I have a table structure like below

DECLARE @XTable TABLE 
(
  ColA Varchar(20), 
  ColB Varchar(20),
  DateCol DATE
)

INSERT INTO @XTable 
VALUES
    ('A', 'X1', '4/1/2015'), ('A', 'X2', '4/10/2015'), ('A', 'X3', '4/12/2015'),
    ('A', 'X4', '4/16/2015'), ('B', 'X1', '5/18/2015'), ('B', 'X2', '5/20/2015')

Expected output:

/*
    ColA   ColB  DateCol      Diff
    A       X1    4/1/2015    0
    A       X2    4/10/2015   9
    A       X3    4/12/2015   2
    A       X3    4/12/2015   11
    A       X4    4/16/2015   15
    A       X4    4/16/2015   5
    A       X4    4/16/2015   4
    B       X1    5/18/2015   0
    B       X2    5/20/2015   12
*/

for example: A X4 will have a difference of date from A X1, A X2 and A X3 & A X3 will have a difference of date from A X1 & A X2

I am able to get difference from last row via below query

;WITH Dataf
    AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY  ColA,ColB, DateCol) AS RowNum
    FROM 
        @XTable
    )

    SELECT a.ColA, a.ColB, SUM(DATEDIFF(Dd,b.DateCol,a.DateCol)) as TotalTime
    FROM 
     Dataf AS A
     LEFT OUTER JOIN Dataf AS B   
                ON A.RowNum = B.RowNum + 1 and a.ColA = b.ColA
    GROUP BY a.ColA, a.ColB

Thought of applying multiple CTE, here is on what I am working now

;WITH Dataf
AS (
SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY ColA ORDER BY  DateCol) AS RowNum
FROM 
    @XTable
),

CTE AS 
(
   SELECT ColA, ColB, DateCol, RowNum, NULL AS DateDifference
   FROM Dataf WHERE RowNum = 1
   UNION ALL
   SELECT DF.ColA, DF.ColB, DF.DateCol, DF.RowNum ,
            DATEDIFF(DD, CT.DateCol, DF.DateCol) AS DateDifference
   FROM Dataf DF
    JOIN  CTE CT ON DF.ColA = CT.ColA AND   DF.RowNum = CT.RowNum + 1

)
SELECT * 
FROM CTE 
ORDER BY ColA

Answer 1

You can instead use LEFT OUTER JOIN to the CTE you prepared. Here is how it can be done

    ;WITH DataForm
    AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Cola ORDER BY  DateCol) AS RowNum
    FROM 
        @XTable
    )


SELECT ColA, ColB, DateCol, 0
FROM DataForm WHERE RowNum = 1
UNION
SELECT T1.ColA, T1.ColB, T1.DateCol
, DATEDIFF(dd,T2.DateCol, T1.Datecol) 
FROM DataForm T1
LEFT OUTER JOIN DataForm T2 ON T1.ColA = T2.ColA            
    AND T1.RowNum >= T2.RowNum 
WHERE DATEDIFF(dd,T2.DateCol, T1.Datecol) > 0

SQL Fiddler Example