Here is the test data -

Create Table #CardHistory

(

SerialNo INT IDENTITY(1,1),

ModificationDatetime DATETIME DEFAULT GETDATE(),

OldCardNo INT,

NewCardNo INT

)

Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 100,101)

Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 101,102)

Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL, 102,103)

Insert #CardHistory(ModificationDatetime,OldCardNo,NewCardNo) VALUES (NULL,...

Yes right but could not find any solution yet ...

Thanks

There are multiple set of such data.

Example:

CardNo 100 changed to 101

CardNo 101 changed to 102

CardNo 102 changed to 103

CardNo 103 changed to 104

CardNo 200 changed to 201

CardNo 201 changed...