How to get the difference in dates between 2 rows (for each same group)? SQL-CodePudding

Hey I've got this Table:

Case         Test      Result       CreationDate
--------------------------------------------------
00123         P        4.7         2022-05-22 10:45:00
00123         P      Cancelled     2022-05-22 09:46:00
00548         P        6.8         2022-05-22 09:45:00
00289         P        4.4         2022-04-12 17:34:00
00289         P                    2022-04-12 16:21:00
00118         P        4.3         2022-04-10 12:56:00
00118         P        4.2         2022-04-10 12:34:00

Sometimes when the result is 'Cancelled'/NULL/Blank/whatever... they do the test again (sometimes even when the result is ok like in case 00118)

What I want is to know the difference between the CreationDate for each Case.

sometimes they do the test one so there will be only one Case and thats ok :)

desirable output (I dont care how the difference will show as long as its not nvarchar):

Case         Test      Result       CreationDate               Difference
---------------------------------------------------------------------------------
00123         P        4.7         2022-05-22 10:45:00         00:59:00 /59:00 /59 (I dont care how as long as I could do avarage later) 
00123         P      Cancelled     2022-05-22 09:46:00         NULL/ Blank
00548         P        6.8         2022-05-22 09:45:00         NULL/ Blank
00289         P        4.4         2022-04-12 17:34:00         01:13:00/ 01:13/ 73
00289         P                    2022-04-12 16:21:00         NULL/ Blank
00118         P        4.3         2022-04-10 12:56:00         00:22:00/ 00:22/ 22
00118         P        4.2         2022-04-10 12:34:00         NULL/ Blank

CodePudding user response：

This is a simple version of the LAG function for the problem above. You could use LEAD function just as well to retrieve the next date. Note to avoid NULLs in the PreviousDate column ISNULL is used, this is simply to ensure we have something to return and indicate where the date/times are the same.

WITH CTETestValues AS (
SELECT 
[Case],
Test,
Result,
CreationDate,
ISNULL(LAG(CreationDate,1)OVER(PARTITION BY [Case] ORDER BY CreationDate),CreationDate) PreviousDate
FROM dbo.TestValues
)

SELECT 
[Case],
Test,
Result,
CreationDate,
PreviousDate,
DATEDIFF(second,PreviousDate,CreationDate) LapsedSeconds
FROM CTETestValues

Hope this assists.