I am having Transactions table as follows in SQL SERVER.
UserID TranDate Amount
1 | 2015-04-01 | 0
1 | 2015-05-02 | 5000
1 | 2015-09-07 | 1000
1 | 2015-10-01 | -4000
1 | 2015-10-02 | -700
1 | 2015-10-03 | 252
1 | 2015-10-03 | 260
1 | 2015-10-04 | 1545
1 | 2015-10-05 | 1445
1 | 2015-10-06 | -2000
I want to query this table to get available balance at any particular date. So I used Windowing function for that.
SELECT TransactionDate,
SUM(Amount) OVER (PARTITION BY UserId ORDER BY TransactionDate ROWS
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM Transactions
But as transactions table is having duplicate entry for date 2015-10-03 it is repeating data for date 2015-10-03. Whenever there is same date I am expecting the last record of that date with available balance summed up.
Current output
TransactionDate AvailableBalance
2015-04-01 | 0
2015-05-02 | 5000
2015-09-07 | 6000
2015-10-01 | 2000
2015-10-02 | 1300
2015-10-03 | 1552
2015-10-03 | 1804
2015-10-04 | 3349
2015-10-05 | 4794
2015-10-06 | 2794
Expected: I want to remove below record from the above result set.
2015-10-03 | 1552
HERE is my sql fiddle
You can SUM
before windowed function like:
WITH cte AS
(
SELECT TransactionDate, UserId, SUM(Amount) AS Amount
FROM Transactions
GROUP BY TransactionDate, UserId
)
SELECT TransactionDate,
SUM(Amount) OVER (PARTITION BY UserId ORDER BY TransactionDate ROWS
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS AvailableBalance
FROM cte