Compare a row with ALL the previous rows in SQL

Question

I've got the following data (Table 1) and I want to calculate the balance at the end of each Gaming Date. However, when the Date Redeemed is less than the Gaming Date that means the amount needs to be subtracted from the ending balance. How do I write a statement that is looking at the Gaming Date in a specific row and the Date Redeemed in ALL previous rows and takes the sum amount of the rows where the Gaming Date is greater that the Date Redeemed (Table 2) Table2

Table1

DateRedeemed GamingDate Trip Amount
13/07/2017   03/07/2017 8001 100
17/07/2017   03/07/2017 8001 150
18/07/2017   04/07/2017 8001 125
27/07/2017   16/07/2017 8001 250
28/07/2017   16/07/2017 8001 310
27/07/2017   17/07/2017 8001 125
31/07/2017   18/07/2017 8001 75
28/07/2017   27/07/2017 8001 80
31/07/2017   28/07/2017 8001 100

Table 2

DateRedeemed GamingDate Trip Amount Running
13/07/2017   03/07/2017 8001 100    100
17/07/2017   03/07/2017 8001 150    250
18/07/2017   04/07/2017 8001 125    375
27/07/2017   16/07/2017 8001 250    525
28/07/2017   16/07/2017 8001 310    835
27/07/2017   17/07/2017 8001 125    810
31/07/2017   18/07/2017 8001 75     760
28/07/2017   27/07/2017 8001 80     590
31/07/2017   28/07/2017 8001 100    610

Please put your table in text format not as image with sample data. — Suraj Kumar
You will want to use a window function. select *, amount - sum(amount) over (order by GamingDate) or some variation. — S3S
Add a case expression to the code posted above by @scsimon to accommodate the negative values and that should do exactly what you need. I would suggest that your data design is a little brittle using the two date columns to indicate positive or negative values. No idea how to offer suggestions because it isn't really clear what this is trying to do. — Sean Lange
Do I understand correctly that the "Running" of the 4th record equals 525 (150+125+250) because the first record doesn't count since it has a DateRedeemed of 13/07/2017 that's lower than the GamingDate of 16/07/2017? — LukStorms

Sentinel Sentinel · Accepted Answer · 2019-01-14T19:10:11

There may be a different solution using analytic functions instead of a scalar sub query, however, I haven't quite figured it out yet. Meanwhile here's this solution:

with t1 as (
  select yd.*
       , ROW_NUMBER() OVER (PARTITION BY trip ORDER BY GamingDate, DateRedeemed) rn
    from YourData yd
)
select t1.*
     , (select sum(amount) from t1 t2
         where t2.trip = t1.trip
           and t2.rn <= t1.rn
           and t2.gamingdate <= t1.gamingdate
           and t1.gamingdate < t2.dateredeemed) Running
  from t1
 order by trip, rn;

DateRedeemed        | GamingDate          | Trip | Amount | rn | Running
:------------------ | :------------------ | ---: | -----: | :- | ------:
13/07/2017 00:00:00 | 03/07/2017 00:00:00 | 8001 |    100 | 1  |     100
17/07/2017 00:00:00 | 03/07/2017 00:00:00 | 8001 |    150 | 2  |     250
18/07/2017 00:00:00 | 04/07/2017 00:00:00 | 8001 |    125 | 3  |     375
27/07/2017 00:00:00 | 16/07/2017 00:00:00 | 8001 |    250 | 4  |     525
28/07/2017 00:00:00 | 16/07/2017 00:00:00 | 8001 |    310 | 5  |     835
27/07/2017 00:00:00 | 17/07/2017 00:00:00 | 8001 |    125 | 6  |     810
31/07/2017 00:00:00 | 18/07/2017 00:00:00 | 8001 |     75 | 7  |     760
28/07/2017 00:00:00 | 27/07/2017 00:00:00 | 8001 |     80 | 8  |     465
31/07/2017 00:00:00 | 28/07/2017 00:00:00 | 8001 |    100 | 9  |     175

I do realize that the last two rows do not match the sample result, however, I think that's due to an error in the sample result rather than an error in my code. If you can explain why the sample result is correct for those two records I can try to rework my code.

[EDIT]

Here's an alternate version that avoids the inefficient scalar subquery in favor of using an analytic function:

With Actions as (
  select *, GamingDate ActionDate, 1 DBCR from Table1
  union all
  select *, DateRedeemed ActionDate, -1 DBCR from Table1
), Analytics as (
  select *
       , sum(Amount*DBCR) over (partition by trip
                                    order by ActionDate, dbcr
                                           , GamingDate, DateRedeemed) Running
    from Actions 
)
select DateRedeemed, GamingDate, Trip, Amount, Running
  from Analytics
 where dbcr = 1

Compare a row with ALL the previous rows in SQL

1 Answers