daily pastebin goal
63%
SHARE
TWEET

Untitled

a guest Jan 18th, 2019 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ID    CustomerId    InvoiceVal    
  2. 1     12            1000          
  3. 2     11            2000  
  4. 3     10            5000  
  5. 4     14            15000  
  6. 5     12            100  
  7. 6     16            8000  
  8. 7     18            3200
  9.    
  10. ID    InvoiceId    Payment
  11. 1     1            150  
  12. 2     3            50
  13. 3     1            120
  14. 4     1            100
  15. 5     5            90
  16. 6     4            7500
  17.    
  18. No.    InoviceId    CustomerId    Payment    Owed
  19. 1      1            12            150        850
  20. 2      1            12            120        730
  21. 3      1            12            100        630
  22.    
  23. DECLARE @invid int = '1'
  24.  
  25. DECLARE @invoicetotal numeric(18,2)
  26.  
  27. SET @invoicetotal =
  28.     (
  29.         SELECT
  30.             [dbo].[TableA].[InvoiceVal]
  31.         FROM [dbo].[TableA]
  32.         WHERE
  33.             ([dbo].[TableA].[ID] = @invid)
  34.     )
  35.  
  36. SELECT
  37.     *,
  38.     SUM(@invoicetotal - [dbo].[TableB].[Payment]) OVER(ORDER BY [dbo].[TableB].[ID] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS [Owed]
  39. FROM [dbo].[TableB]
  40. WHERE
  41.     ([dbo].[TableB].[InvoiceId] = @invid)
  42.    
  43. ID    InvoiceId    Payment    Owed
  44. 1     1            150.00     NULL
  45. 3     1            120.00     850.00
  46. 4     1            100.00     1730.00
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