daily pastebin goal
33%
SHARE
TWEET

Untitled

a guest May 15th, 2012 64 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top