Subtract by Records

Transact-SQL
1

I have the Date and Units columns in a table. I am trying to achieve and get the Remaining column. The starting is 100 units.

Date Units Remaining
6/1/2018 2 98
6/2/2018 10 88
6/3/2018 15 73
6/4/2018 22 51
6/5/2018 5 46
6/6/2018 4 42
6/7/2018 3 39
6/8/2018 2 37
6/9/2018 1 36
6/10/2018 3 33
2 
CREATE TABLE TableA
    ([Date] datetime, [Units] int, [Remaining] int)
;
    
INSERT INTO TableA
    ([Date], [Units], [Remaining])
VALUES
    ('2018-06-01 00:00:00', 2, 98),
    ('2018-06-02 00:00:00', 10, 88),
    ('2018-06-03 00:00:00', 15, 73),
    ('2018-06-04 00:00:00', 22, 51),
    ('2018-06-05 00:00:00', 5, 46),
    ('2018-06-06 00:00:00', 4, 42),
    ('2018-06-07 00:00:00', 3, 39),
    ('2018-06-08 00:00:00', 2, 37),
    ('2018-06-09 00:00:00', 1, 36),
    ('2018-06-10 00:00:00', 3, 33)
;



SELECT [Date], [Units]
   ,100 - SUM(Units) OVER(ORDER BY Date ASC) AS [Remaining_Calculated]
   ,[Remaining]
FROM TableA

output:

Date Units Remaining_Calculated Remaining
01/06/2018 00:00:00 2 98 98
02/06/2018 00:00:00 10 88 88
03/06/2018 00:00:00 15 73 73
04/06/2018 00:00:00 22 51 51
05/06/2018 00:00:00 5 46 46
06/06/2018 00:00:00 4 42 42
07/06/2018 00:00:00 3 39 39
08/06/2018 00:00:00 2 37 37
09/06/2018 00:00:00 1 36 36
10/06/2018 00:00:00 3 33 33

dbfiddle

3

Pretty Awesome!

4

BTW... Would you know how to do this in SSRS?

5

Try do add this logic in your dataset ( your query or your stored procedure)
or use a function :
=RunningValue(Fields!Score.Value,sum,"DataSet1")

see here

6

hi

i know this post is a long time ago ... also i know solution has been provided

I dont have sql server 2012 .. cannot use sum() over() used in solution

i have sql server 2008

i have come up with a different solution ... using recursive cte

... seniors .. bitsmed and Scott Pletcher do not recommend
recursive cte due to performance issues
however since this is a small data set
:slight_smile:
:slight_smile:

drop create data 
drop table tableA 
go 



CREATE TABLE TableA
    ([Date] datetime, [Units] int, [Remaining] int)
;
    
INSERT INTO TableA
    ([Date], [Units], [Remaining])
VALUES
    ('2018-06-01 00:00:00', 2, 98),
    ('2018-06-02 00:00:00', 10, 88),
    ('2018-06-03 00:00:00', 15, 73),
    ('2018-06-04 00:00:00', 22, 51),
    ('2018-06-05 00:00:00', 5, 46),
    ('2018-06-06 00:00:00', 4, 42),
    ('2018-06-07 00:00:00', 3, 39),
    ('2018-06-08 00:00:00', 2, 37),
    ('2018-06-09 00:00:00', 1, 36),
    ('2018-06-10 00:00:00', 3, 33)
;
go
SQL 
;WITH rownumber_adding_cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) AS rn, 
                [date], 
                [units], 
                [remaining] 
         FROM   tablea), 
     recursive_cte 
     AS (SELECT *, 
                [remaining] AS Remaining_Calculated 
         FROM   rownumber_adding_cte 
         WHERE  rn = 1 
         UNION ALL 
         SELECT a.*, 
                b.remaining_calculated - a.units 
         FROM   rownumber_adding_cte a 
                JOIN recursive_cte b 
                  ON a.rn = b.rn + 1) 
SELECT * 
FROM   recursive_cte 

go
Result

image

1 Like