Guest User

Untitled

a guest
Jan 29th, 2018
67
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.06 KB | None | 0 0
  1. PAYMENTS looks like this:
  2. BEN12, FLY, 8.00
  3. BEN12, CAR, 2.00
  4. MKE10, FLY, 3.00
  5.  
  6. and Applicaitons needs to look like this:
  7. BEN12, 1234-10,5.00
  8. BEN12, 1234-11,3.00
  9. BEN12, 2234-20,2.00
  10. MKE10, 1234-13,3.00
  11.  
  12. `declare @currentCust varchar(20) =''
  13. declare @LastCust varchar(20) =''
  14. declare @AmtBal decimal (5,2) ='50'
  15. declare @applyBal decimal (5,2) ='0'
  16. declare @CurrentDoc varchar(20) =''
  17. declare @LastDoc varchar(20) =''
  18. declare @CurrentCod varchar(20) =''
  19. declare @LastCod varchar(20) =''
  20.  
  21. begin
  22. UPDATE USER_PRD_APPLY
  23. set CODE = DOCS.DED,
  24. @CurrentDoc = DOCS.doc_NO,
  25. @currentCust = DOCS.CUST_NO,
  26. @CurrentCod = DOCS.DED,
  27. CUST_ID = DOCS.CUST_NO,
  28. @AmtBal = case when @currentCust = @LastCust THEN @AmtBal ELSE RCV.AMT END,
  29. @applyBal = case
  30. WHEN DOCS.DED = '608' and DOCS.balance <= @AmtBal
  31. THEN DOCS.balance
  32. when DOCS.DED = '608' and DOCS.balance > @AmtBal
  33. THEN @AmtBal
  34. @LastCust= DOCS.CUST_NO,
  35. @AmtBal = @AmtBal- @applyBal
  36.  
  37. from DOCUMENTS DOCS left join RECEIVE RCV
  38. on DOCS.CUST_NO = RCV.CUST_ID and DOCS.DED = RCV.CODE
  39.  
  40. end
Add Comment
Please, Sign In to add comment