Search code examples
sqlt-sqlgroup-bycomparesqlcompare

TSQL - comparing grouped values within a table

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.

Solution

  • 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