sql-serverover-clause

How to remove the duplicate records in select query over clause


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


Solution

  • You can SUM before windowed function like:

    SqlFiddleDemo

    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