How to remove the duplicate records in select query over clause

Question

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

score 4 · Accepted Answer · answered Oct 07 '15 at 10:46

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

Vladimir Baranov · Answer 2 · 2015-10-07T11:21:39.597

1

Use RANGE instead of ROWS.

SQL Fiddle

SELECT
  TransactionDate,  
  SUM(Amount) OVER (
    PARTITION BY UserId 
    ORDER BY TransactionDate 
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS AvailableBalance
FROM Transactions;

This variant produces a different result set than originally requested, but it may be useful in some cases. This variant returns same number of rows as in Transactions table. So, it will return two rows with 2015-10-03, but for both rows AvailableBalance would be 1804.

I just wanted to highlight that there is that option RANGE. If you really need one row per day, then grouping by day at first as in the answer by @lad2025 is the way to go.

edited Oct 07 '15 at 11:21

answered Oct 07 '15 at 11:11

Vladimir Baranov

31,799
5
53
90

OP will have to add `DISTINCT`. Two duplicate keys are brought back `2015-10-03 1804` – Evaldas Buinauskas Oct 07 '15 at 11:14
@EvaldasBuinauskas, yes, you are right. This variant produces a different result set than originally requested, but it may be useful in some cases. I just wanted to highlight that there is that option `RANGE`. If you really need one row per day, then grouping by day at first as in the answer by @lad2025 is the way to go. – Vladimir Baranov Oct 07 '15 at 11:18
Not arguing. It's indeed a very useful tip. – Evaldas Buinauskas Oct 07 '15 at 11:20

How to remove the duplicate records in select query over clause

2 Answers2