Below is the table I have created and inserted values in it:

CREATE TABLE Invoices     
(
    InvID int,     
    InvAmount int
)
GO    
    
INSERT INTO Invoices 
VALUES (1, 543), (2, 749)

CREATE TABLE payments    
(
    PayID int IDENTITY (1, 1),     
    InvID int,     
    PayAmount int,    
    PayDate date
)

INSERT INTO payments
VALUES (1, 20, '2016-01-01'),
       (1, 35, '2016-01-07'),
       (1, 78, '2016-01-13'),
       (1, 52, '2016-01-25'),    
       (2, 40, '2016-01-03'),
       (2, 54, '2016-01-15'),  
       (2, 63, '2016-01-17'),
       (2, 59, '2016-01-28') 

SELECT * FROM Invoices 
SELECT * FROM payments

As shown in the screenshot above, the Invoice table specifies various customer billings (the first billing totals 543, the second billing totals 749).

As shown in the screenshot above, the payments table specifies the various payments the customer made for each of the billings. For example, one can see that on January 1st the customer paid 20 USD out of billing no. 1 (which totals 543 USD), and on January 3rd the customer paid 40 USD out of billing no. 2, (which totals 749 USD).

Now the question is:

Write a query that displays the billing balance, based on the number of payments made so far.

The query result should exactly look like the screenshot below:

This is what I have tried:

SELECT 
    payments.InvID,
    InvAmount - SUM(PayAmount) OVER (PARTITION BY payments.InvID ORDER BY PayID
                                     ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) AS 'InvAmount',
    PayDate, PayAmount,
    InvAmount - SUM(PayAmount) OVER (PARTITION BY payments.InvID ORDER BY PayID) AS 'Balance'
FROM 
    Invoices
JOIN 
    payments ON payments.InvID = Invoices.InvID

After running the query, I got the following result which is shown below:

As you can see from the screenshot above, I nearly got the result I wanted.

The only problem is that InvAmount is exactly returning the same row values as Balance. I am not able to retain the starting row values of InvAmount which are 543 (InvID = 1) and 749 (InvID = 2) respectively.

How can this issue be solved?

CodePudding user response：

You can add back the PayAmount in the calculation

InvAmount 
  PayAmount
- SUM(PayAmount) OVER (PARTITION BY payments.InvID 
                           ORDER BY PayID
                                 ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND 0 FOLLOWING) AS InvAmount

Or use BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING. But you need to handle NULL value for the very first row

InvAmount 
- ISNULL(SUM(PayAmount) OVER (PARTITION BY payments.InvID 
                           ORDER BY PayID
                                 ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND 1 PRECEDING), 0) AS InvAmount

db>fiddle demo

CodePudding user response：

You can use PARTITION BY clause for invoice identifier and then deduct the pay amount from the invoice amount as given below

;WITH CTE_Balance as
    (
    SELECT i.InvID, i.InvAmount, p.PayAmount, p.PayDate
         , InvAmount - sum(payamount) over (partition by p.invid order by paydate rows between unbounded preceding and current row) as balance 
         , ROW_NUMBER() over(partition by p.invid order by p.paydate) as rnk
    FROM payments as p
    inner join Invoices as i
    on i.InvID = p.InvID
    )
    SELECT invid, case when rnk =1 then invamount else lag(balance) over(partition by invid order by paydate) end as invamount 
           ,payAmount, paydate, balance
    FROM CTE_Balance