TSQL - comparing grouped values within a table

Question

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.

Answer 1

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 

Answer 2

        SELECT  grp_ID_1, metric, state, value,       
                (SELECT  MAX(value) 
                    FROM tablename   
                 ) - value AS Difference

        FROM tablename  group by state, grp_ID_1, metric, value  
        having state = 'OH'