CODEWITHSUNDEEP
sqlsql-serverselect
Suiseidl
Suiseidl

Reputation: 3

Use a value from a line before in SQL?

I have a query with a few columns of a table and now I want a new column that offsets the value of two columns with the value of the row above. The first line is the base value for the calculation.

ID date val1 val2
1 2022-01-11 12000 NULL
2 2022-02-11 500 NULL
3 2022-02-11 NULL 200
4 2022-03-11 NULL 400

So in this example 12000 is the first line therefore the base value. The second line should take the base value (from the line above) and add the 500. So the result is 12500 in this line. The third line should take the result from above 12500 and subtract the 200 from v2. So the result in this line is 12300. And so on.. The formula is therefore: result (from line above) + v1 - v2

What I expect is this:

ID date val1 val2 result
1 2022-01-11 12000 NULL 12000
2 2022-02-11 500 NULL 12500
3 2022-02-11 NULL 200 12300
4 2022-03-11 NULL 400 11900

The query so far is:

SELECT ID, date, val1, val2
FROM Table
ORDER BY ID

How can I get the result column to this select-query?

Could anyone help me please?

I didn't really try anything because I absolutely don't know how to get a value from a line above.

Upvotes: 0

Views: 201

Answers (1)

Tim Jarosz
Tim Jarosz

Reputation: 1178

You can use the SUM() function to do a running total over the entire query results. Here I'm using the SUM() and specifying val1 - val2 inside the function. Reference learning material: https://codingsight.com/calculating-running-total-with-over-clause-and-partition-by-clause-in-sql-server/

CREATE TABLE dataTable (
  ID int not null
  , [date] datetime not null
  , val1 int null
  , val2 int null
);

INSERT INTO dataTable (ID, [date], val1, val2)
VALUES (1, '2022-01-11', 12000, null)
  , (2, '2022-02-11', 500, null)
  , (3, '2022-02-11', null, 200)
  , (4, '2022-03-11', null, 400)
;

SELECT ID, date, val1, val2
  , SUM (ISNULL(val1,0) - ISNULL(val2,0)) OVER (ORDER BY ID) AS result
FROM dataTable
ORDER BY ID
ID date val1 val2 result
1 2022-01-11 00:00:00.000 12000 null 12000
2 2022-02-11 00:00:00.000 500 null 12500
3 2022-02-11 00:00:00.000 null 200 12300
4 2022-03-11 00:00:00.000 null 400 11900

fiddle

Upvotes: 1

Related Questions