CODEWITHSUNDEEP
sqlt-sql
Megrez7
Megrez7

Reputation: 1457

Calculate column value based on previous row and the same calculated column

I am struggling with calculating Calc based on Val1 and Val2.

Calc = previous_row.Calc + previousr_row.Val1 - previous_row.Val2

Input data are ordered by Date.

Expected output:

+---------+--------+------------+------+
|  Val1   |  Val2  |    Date    | Calc |
+---------+--------+------------+------+
| 0,00    | 0,00   | 2016-01-01 |    0 |
| 1000,00 | 0,00   | 2020-01-01 |    0 |
| 0,00    | 0,00   | 2020-01-15 | 1000 |
| 0,00    | 500,00 | 2020-02-01 | 1000 |
| 0,00    | 300,00 | 2020-03-01 |  500 |
| 0,00    | 0,00   | 2020-03-15 |  200 |
| 0,00    | 200,00 | 2020-04-01 |  200 |
+---------+--------+------------+------+

Tried LAG function already, successfully getting data from previous row, but I was not able to get Calc calculated value from the previous:

LAG(Val1) OVER (ORDER By Date) - LAG(Val2) OVER (ORDER BY Date)

In real world scenario I will add PARTITION BY, but that is different story. Would like to keep it simple for now.

UPDATE: Inspired by others:

SUM(Val1) OVER(ORDER BY Data) - SUM(Val2) OVER(ORDER BY Data) - Val1 + Val2 AS Calc

While it calculates proper values, is this efficient?

I am using latest SQL Server 2019 / Azure SQL.

Upvotes: 0

Views: 1588

Answers (2)

somaye javidmoradi
somaye javidmoradi

Reputation: 197

    ALTER FUNCTION calc (@lagDate DATE)
    RETURNS INT
    AS
    BEGIN
        IF @lagDate IS NULL
            RETURN 0

        DECLARE @r INT

        SELECT @r = isnull(lag(val1) OVER ( ORDER BY [date] ), 0) - 
                    isnull(lag(val2) OVER ( ORDER BY [date] ), 0) + 
                    dbo.calc(lag([date]) OVER ( ORDER BY [date] ))
        FROM dbo.ss
        WHERE [date] <= @lagDate

        RETURN @r
    END
    GO

  SELECT *
        ,dbo.calc([date]) calc
    FROM dbo.ss

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I think you are looking for cumulative sum functions:

select t.*,
       max(val1) over (order by date) - sum(val2) over (order by date)
from t;

Upvotes: 1

Related Questions