question

Mandar Alawani avatar image
Mandar Alawani asked

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?
sql-server-2005t-sqlrank
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@SQLShark avatar image @SQLShark commented ·
Is there only one of each per transaction?
0 Likes 0 ·

1 Answer

·
@SQLShark avatar image
@SQLShark answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.