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
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
invid | invamount | payAmount | paydate | balance |
---|---|---|---|---|
1 | 543 | 20 | 2016-01-01 | 523 |
1 | 523 | 35 | 2016-01-07 | 488 |
1 | 488 | 78 | 2016-01-13 | 410 |
1 | 410 | 52 | 2016-01-25 | 358 |
2 | 749 | 40 | 2016-01-03 | 709 |
2 | 709 | 54 | 2016-01-15 | 655 |
2 | 655 | 63 | 2016-01-17 | 592 |
2 | 592 | 59 | 2016-01-28 | 533 |