Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on May 15th, 2012  |  syntax: T-SQL  |  size: 0.80 KB  |  views: 49  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. declare @poId int
  2. set @poId = 130153;
  3.  
  4. with ChequeNumChanges(ModifiedDate, Val1, Val2, rN) as(
  5.         select poDate, poChequeNumber, null, 0 from dbo.PurchaseOrders where poID = @poId union all
  6.         select ModifiedDate, OldValue, NewValue, ROW_NUMBER() over(order by id) as rN
  7.         from dbo.POAudit where poId = @poId and FieldName = 'poChequeNumber'
  8. )
  9. select * from(
  10.         select case when c1.rN = 0 and c2.ModifiedDate is null then c1.Val1
  11.                                 when c1.rN = 0 then c2.Val1
  12.                                 else c1.Val2 end as num,
  13.         c1.ModifiedDate as DateIn, c2.ModifiedDate as DateOut
  14.         from ChequeNumChanges as c1
  15.         left join ChequeNumChanges as c2 on c1.rN = c2.rN - 1
  16. ) as t
  17. inner join dbo.POAudit as a on t.num = a.ForeignKey and a.ForeignKeyTable = 'c'
  18. --where a.ModifiedDate >= t.DateIn and (a.ModifiedDate < t.DateOut or t.DateOut is null)