Reputation: 8478
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
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