CODEWITHSUNDEEP
sql-serveraveragemoving-averagevariance
VS1SQL
VS1SQL

Reputation: 155

Calculating Variance between two days in a column

I have this table below:

Fruit|  date    |  profit | Rolling_Avg
Apple|2014-01-16|   5.61  | 0.80
Apple|2014-01-17|   3.12  | 1.25
Apple|2014-01-18|   2.20  | 1.56
Apple|2014-01-19|   3.28  | 2.03
Apple|2014-01-20|   7.59  | 3.11
Apple|2014-01-21|   3.72  | 3.65
Apple|2014-01-22|   1.11  | 3.80
Apple|2014-01-23|   5.07  | 3.73

What I'm trying to do is calculate the variance by dates in increments of 1 so for example the returned table would be like:

Fruit|  date    |  profit | Rolling_Avg| Variance %
Apple|2014-01-16|   5.61  | 0.80       |  
Apple|2014-01-17|   3.12  | 1.25       |-0.443850267
Apple|2014-01-18|   2.20  | 1.56       |-0.294871795
Apple|2014-01-19|   3.28  | 2.03       | 0.490909091
Apple|2014-01-20|   7.59  | 3.11       | 1.31402439
Apple|2014-01-21|   3.72  | 3.65       |-0.509881423
Apple|2014-01-22|   1.11  | 3.80       |-0.701612903
Apple|2014-01-23|   5.07  | 3.73       | 3.567567568

I am not sure how to go about doing this.

I suppose if the dates were headers instead of rows it would be easier to calculate variance % as 

sum(([2014-01-17] - [2014-01-16])/[2014-01-16])) as [variance %]

but again not entirely sure this is the most efficient way to do this

Upvotes: 2

Views: 1665

Answers (3)

shishir
shishir

Reputation: 851

you may try using LAG

Below is the Query which might give you the desired result.

Query

select fruit,vdate,profit,rolling_avg,
    LAG(profit,1,0) over(order by day(vdate)) as previousprofit,
    ((profit-LAG(profit) over(order by day(vdate)))/LAG(profit) over(order by day(vdate))) as variance_percent
from variance

Fiddle

Upvotes: 2

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

If you are using SQL Server 2012+ you can use lag as below:

Select *, (Profit - PrevProfit)/PrevProfit as [variance %] from (
    Select *, PrevProfit = lag(profit) over(partition by fruit order by [date]) 
    from #fruitdata
) a

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You are not calculating the "variance" -- which has a specific definition in statistics -- but the "difference". For this, use lag():

select t.*,
       (rolling_avg - 
        lag(rolling_avg) over (order by date)
       ) as diff
from t;

I would do the difference as "recent" - "previous". You seem to be going the other way, so just reverse the order of the operands.

Upvotes: 1

Related Questions