Running Total Sum Minus in SQL Server-CodePudding

I am trying to calculate a minus running total in SQL with this code but it is not giving me the expected result. After the date 01/2021, I would like to minus the sales for each month.

select Name, Date, Sales, MinusRunningTotal = B.Sales - SUM(A.sales) 
              OVER (PARTITION BY Name ORDER BY Date)
From TableA A 
Join TableB B on A.ID = B.ID
Where Date > '01/2021'

This is how the data is displayed

Name Date      Sales    
A    01/2021    10         
A    02/2021    1           
A    03/2021    2           
A    04/2021    3

This is what I want to achieve

Name Date      Sales    MinusRunningTotal
A    01/2021    10         10
A    02/2021    1           9
A    03/2021    2           7
A    04/2021    3           4

CodePudding user response：

If that data already exists in a table with name, date, and sales columns, try:

SELECT [name],
       [date],
       [sales],
       FIRST_VALUE([sales]) OVER (PARTITION BY [name]
                                      ORDER BY [date]) * 2 
           -   SUM([sales]) OVER (PARTITION BY [name]
                                      ORDER BY [date]
                      ROWS UNBOUNDED PRECEDING) AS minus_running_total                                      
 FROM my_table

sql fiddle

This computes all preceding sales for the current name (including current value)

`SUM([sales]) OVER (PARTITION BY [name] 
                        ORDER BY [date]`)

This computes the first chronological value for the current name, X 2:

`FIRST_VALUE([sales]) OVER (PARTITION BY [name]
                                ORDER BY [date]) * 2`

So the first row computes as (10 x 2) - 10 = 10

Second row is (10 x 2) - (10 1) = 9

Third row is (10 x 2) - (10 1 2) = 7

etc

CodePudding user response：

select Name, Date, Sales, 
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date) as RN into 
#temp2 From table 

select Name, Date, Sales, 
case when rn != 1 
then (select sale from #temp2 where RN = 1) - sum(sales) 
              OVER (PARTITION BY Name ORDER BY Date
              rows between UNBOUNDED PRECEDING and current row) 
          else sales end
From #temp2

CodePudding user response：

I made a table sss the contains the data you show in your second code section. This query will give the results you want. If you need to join in other data, do it in the CTE called xs below.

with
xs as (
    select name, dt, sales, sales tot,
        ROW_NUMBER() over (partition by name order by dt) n
    from sss
    ),
rec as (
    select * from xs where n = 1
    
    union all
    
    select xs.name, xs.dt, xs.sales, rec.tot - xs.sales, xs.n
    from rec
    join xs on rec.name = xs.name
        and xs.n = rec.n   1
    )
select *
from rec

How it works:

In the CTE (common table expression) xs, we number the rows associated with a given name in ascending dt order.

CTE rec is a recursive query that begins by fetching the first row of the group associated with a name via filtering with "n = 1". That becomes the first row of the output. The second part of rec fetches succeeding rows where n of the new row equals n 1 of the previous row. The desired running total, kept in column tot, is gotten by subtracting the new row's sales from the previous rows tot.