a guest May 15th, 2012 64 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
- declare @poId int
- set @poId = 130153;
- with ChequeNumChanges(ModifiedDate, Val1, Val2, rN) as(
- select poDate, poChequeNumber, null, 0 from dbo.PurchaseOrders where poID = @poId union all
- select ModifiedDate, OldValue, NewValue, ROW_NUMBER() over(order by id) as rN
- from dbo.POAudit where poId = @poId and FieldName = 'poChequeNumber'
- select * from(
- select case when c1.rN = 0 and c2.ModifiedDate is null then c1.Val1
- when c1.rN = 0 then c2.Val1
- else c1.Val2 end as num,
- c1.ModifiedDate as DateIn, c2.ModifiedDate as DateOut
- from ChequeNumChanges as c1
- left join ChequeNumChanges as c2 on c1.rN = c2.rN - 1
- ) as t
- inner join dbo.POAudit as a on t.num = a.ForeignKey and a.ForeignKeyTable = 'c'
- --where a.ModifiedDate >= t.DateIn and (a.ModifiedDate < t.DateOut or t.DateOut is null)
RAW Paste Data