1

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.

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.