Calculate Percentage Difference/Variance Between Rows - SQL

Question

Good afternoon,

I am trying to calculate the percentage difference between rows in SQL Server and need help achieving this.

I want to compare the value from [Record_Count] with the previous value from [Record_Count] and then calculate the variance in Percentage between the 2 [Record_Count] Numbers. The below table is an example of the data.

So for example I would want to compare the value in row 268 with the value in row 267 and calculate the % increase/decrease and then compare row 269 with 268 and so on.

ID	Record_Count	Variance
270	543
269	28361
268	548
267	28464

Thank you in advance, any help is appreciated as I'm currently unsure on how to achieve this.

look at analytical functions involving lead/lag. https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver15 These functions allow you to look ahead or behind rows based on an order such as your ID and get a value (such as your record_count) and compare it to values on the row from which it is being led or lagged. An alternative way to do this is join based on ID + or -1 assuming a 1 variance always. or use min/max and a where clause. but lead/lag is much simpler provided your version of SQL-server supports it. In both cases: then do then do the math. — xQbert, Jul 20 '21 at 15:00
Does this answer your question? [Is there a way to access the “previous row” value in a SELECT statement?](https://stackoverflow.com/questions/710212/is-there-a-way-to-access-the-previous-row-value-in-a-select-statement) — Thom A, Jul 20 '21 at 15:05

score 1 · Answer 1 · answered Jul 20 '21 at 15:08

Example so

with q (ID, Record_Count, Record_Count_Prev) as (
select ID, Record_Count, lag(Record_Count) over(order by ID), 
from [Your_table]
order by ID
)
select ID, Record_Count, Record_Count_Prev, case when isnull(Record_Count_Prev, 0) <> 0 then Record_Count / Record_Count_Prev else 0 end
from q;

score 0 · Answer 2 · answered Jul 20 '21 at 15:08

0

select * 
   , (1 - LEAD(Record_Count,1,Record_Count) over (order by id desc)/Record_Count) * 100  as chanegpercentage
from tablename
order by id desc

answered Jul 20 '21 at 15:08

eshirvana

23,227
3
22
38

score 0 · Answer 3 · answered Jul 20 '21 at 18:16

You can use the Window ROW_NUMBER() function to access the next rows and compare columns.

With CTE AS(
  SELECT *,
  ROW_NUMBER() OVER(ORDER BY (SELECT null)) AS seq
FROM variansTB
)
SELECT ID,Record_Count,
    ISNULL((SELECT Record_Count FROM CTE c WHERE c.seq = CTE.seq + 1),0)/Record_Count * 100 AS Variance
FROM CTE

demo in db<>fiddle

Calculate Percentage Difference/Variance Between Rows - SQL

3 Answers3