T-SQL find rows of a later date for each transaction
Hi All, Here is my table, call it transactionhistory ID Action Date version 1 abc 2014-01-01 0 1 pqr 2014-01-03 1 1 xyz 2014-01-05 2 This is correct behavior, i.e. for each txn (ID=1), "abc" action occurs first, then "pqr" and last "xyz" But there are some txns with ID Action Date version 2 abc 2014-01-10 0 2 pqr 2014-01-03 1 2 xyz 2014-01-05 2 Here, the date for action "abc" is much later than others. the version is in correct order but date is not. How do I find such transactions, I was thinking of using RANK but not sure how?
If there is only 1 action per ID then this will work. The ABC Check will check if the ABC date is after PQR. DECLARE @EXAMPLE TABLE ( ID INT , [ACTION] CHAR(3) , [DATE] DATE , VERSION INT ) INSERT INTO @EXAMPLE ( ID, ACTION, DATE, VERSION ) VALUES ( 1, 'abc', '2014-01-01', 0 ) , ( 1, 'pqr', '2014-01-03', 1 ) , ( 1, 'xyz', '2014-01-05', 2 ) , ( 2, 'abc', '2014-01-10', 0 ) , ( 2, 'pqr', '2014-01-03', 1 ) , ( 2, 'xyz', '2014-01-05', 2 ); WITH CTE AS ( SELECT ID , MAX(CASE WHEN ACTION = 'ABC' THEN DATE END) AS 'ABCDATE' , MAX(CASE WHEN ACTION = 'PQR' THEN DATE END) AS 'PQRDATE' , MAX(CASE WHEN ACTION = 'XYZ' THEN DATE END) AS 'XYZDATE' FROM @EXAMPLE GROUP BY ID ) SELECT ID , ABCDATE , PQRDATE , XYZDATE , CASE WHEN ABCDATE > PQRDATE THEN 1 ELSE 0 END AS 'ABC CHECK' FROM CTE