CODEWITHSUNDEEP
sqlsql-servert-sql
CozyAzure
CozyAzure

Reputation: 8478

SQL get previous row's EVALUATED value

Is there a way we can obtain the previous evaluated value in SQL? I can make do with LAG to get the row value, but I am stucked on how to get the calculated values.

My use case is that I am cleaning a series of cumulative data. The desired output is, if the current value is less than the previous value, take the previous value, else use the current value (Reason being cumulative numbers can only gets bigger)

Here is the SQL I have tried to come up with

SELECT 
 tbl.Date,
 tbl.Location,
 tbl.Report,
 tbl.Product,
 tbl.Value,
 CASE WHEN 
    -- If current value is less than prev value take prev value
    (tbl.Value < (LAG(tbl.Value) OVER (ORDER BY tbl.Location_name,tbl.Report_name,tbl.Product,tbl.Date) ))
        THEN LAG(tbl.Value) OVER (ORDER BY tbl.Location_name,tbl.Report_name,tbl.Product,tbl.Date)
 ELSE tbl.Value
 END AS Value2
 FROM TABLEDATA tbl
)

The produced results is 

Date    Location Report  Product    Value       Value2
Jan     Town A   Output  ASH        0.7         0.7
Feb     Town A   Output  ASH        1.7         1.7
Mar     Town A   Output  ASH        0.039514    1.7
Apr     Town A   Output  ASH        0.957533    0.957533
May     Town A   Output  ASH        2.053009    2.053009
Jun     Town A   Output  ASH        4.68674     4.68674

If you look at Apr's data, the data should be 1.7 instead of 0.9, but the SQL is correct in a sense it is comparing 0.957533 with 0.039514, I need the 0.957533 to be compared with the evaluated value of 1.7.

Correct output:

Jan Town A  Output  ASH   0.7           0.7
Feb Town A  Output  ASH   1.7           1.7
Mar Town A  Output  ASH   0.039514      1.7
Apr Town A  Output  ASH   0.957533      1.7
May Town A  Output  ASH   2.053009      2.053009
Jun Town A  Output  ASH   4.68674       4.68674

Upvotes: 0

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You seem to want a cumulative maximum, rather than a comparison to the previous value. The syntax is:

MAX(tbl.Value) OVER (ORDER BY tbl.Location_name, tbl.Report_name, tbl.Product, tbl.Date)

That said, I am guessing that you really want this per location/report/product combination. So, you want a PARTITION BY:

MAX(tbl.Value) OVER (PARTITION BY tbl.Location_name, tbl.Report_name, tbl.Product
                     ORDER BY tbl.Date
                    )

Upvotes: 2

Related Questions