I need to compare grouped data to look for shifts in a calculated value. The output of my current SQL looks something like this...
Grp_ID_1 / Metric / State / Value
A Metric1 OH 50
B Metric1 OH 65
A Metric1 CA 20
B Metric1 CA 35
In the example above, I need to calculate the difference between A-Metric1-OH value of 50 and B-metric1-OH value of 65.
You can use LEAD to calculate difference between rows.
SELECT LEAD(State, 1,0) OVER (ORDER BY Grp_ID_1 ) AS NextState ,
State - LEAD(State, 1,0) OVER (ORDER BY Grp_ID_1 ) AS StateDif
FROM yourTable