SQL Server – Compare Timestamp Difference Between Consecutive Records

sql serversql server 2014t-sql

How do I compare Timestamp difference between 2 consecutive records?

This is my example:

DECLARE @x TABLE(ProductID INT, SingleRunFailure bit, SampleDate date);
INSERT @x VALUES
(101, 1, '2016-01-01 10:09:19.617'),    
(101, 0, '2016-01-05 11:09:19.617'),    
(101, 1, '2016-01-07 12:09:19.617'),
(101, 0, '2016-01-09 12:09:19.617'),    
(101, 1, '2016-01-15 13:09:19.617'),    
(101, 0, '2016-01-17 13:09:19.617'),
(101, 0, '2016-01-29 14:09:19.617'),    
(101, 0, '2016-02-02 15:09:19.617');

;WITH cte(ProductID, CCVPassed)
AS
(      
   SELECT ProductID, CASE WHEN SUM(LastTwoRunsPass) >= 2 THEN 1 ELSE 0 END
   FROM (SELECT ProductID, LastTwoRunsPass = CASE WHEN SingleRunFailure = 0 THEN 1 ELSE 0 END,           
             RowNumber = ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SampleDate DESC)
        FROM @x) AS aggregation
   WHERE RowNumber <= 2
   GROUP BY ProductID   
)

SELECT * FROM cte;

This returns me a Pass or Fail where SingleRunFailure flag was false on last 2 records. This part works as expected.

I need to add logic where if the SampleDate difference on the last 2 records was less than 15 minutes return True.

Expected response:

ProductID    CCVPassed
101          0

This is because the difference between last 2 records is 4 days. If the SampleDate difference between last 2 records was less than 15 minutes CCVPassed would return 1.

Best Answer

This works like this:

  • It gets numbers for each row of each ProductID ordered by SampleDate (ROW_NUMBER()).
  • It only keeps first 2 values (biggest SampleDate which are the last 2 rows of the sample)
  • It calculates the difference in minutes between the 2 SampleDate

Query:

WITH cte AS (
    SELECT ProductID, SampleDate
        , n = ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY SampleDate DESC)
    FROM @x
)
SELECT ProductID
    , CCVPassed = CASE WHEN DATEDIFF(minute, MIN(SampleDate), MAX(SampleDate)) < 15 THEN 'true' ELSE 'false' END
FROM cte
WHERE n <= 2
GROUP BY ProductID

SQL Fiddle

Output:

ProductID   CCVPassed
101 4       false

Note:

  • It partitions by ProductId because it supposed you have several ProductId in your real table.
  • Replace true/false by 1/0 or whatever is needed in the real query
Related Question