Cumulative time difference for each group
I have a table structure as shown 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 Result:
/*
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: X4 will have a date difference from A X1, A X2 and A X3 & Amp; X3 will have date difference between A X1 and A X2
I can get the difference between the last line with the following 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 CTEs is what I'm working on right 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
+3
source to share
1 answer
Instead, you can use the LEFT OUTER JOIN for the CTE you prepared. Here's how to do it
;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
+1
source to share