CODEWITHSUNDEEP
sqlsql-server-2008-r2sql-server-2012
user3036342
user3036342

Reputation: 1045

T-SQL previous year total in next year column

I currently have a table that looks like this:

fscYear ID          Days    #Invoices     AVG
2011    20000807    221     7             27
2012    20000807    403     15            25
2013    20000807    390     14            26
2014    20000807    119     4             23

I would like to include the previous year's AVG in the next year, like so:

fscYear ID          Days    #Invoices   AVG  prevAVG
2011    20000807    221     7           27    0
2012    20000807    403     15          25    27
2013    20000807    390     14          26    25
2014    20000807    119     4           23    26

How I can achieve that?

edit the SQL is straightforward, 

select * from theTableThatHoldsThedata

Upvotes: 2

Views: 164

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

Most databases support ANSI standard window functions. You can do this with lag():

select t.*, coalesce(lag(avg) over (order by by fscyear), 0) as prevAVG
from atable t;

This will work in SQL Server 2012+. For earlier versions, you can use a correlated subquery or apply:

select t.*, coalesce(tprev.prevAvg, 0) as prevAvg
from atable t outer apply
     (select top 1 t2.Avg as prevAvg
      from atable t2
      where t2.fscyear < t.fscyear
      order by t2.fscyear desc
     ) tprev;

Upvotes: 2

Deepshikha
Deepshikha

Reputation: 10284

You can write as:

SELECT Cur.fscYear ,Cur.ID,Cur.Days,Cur.#Invoices,Cur.AVG,  
       isnull(Prv.AVG,0) AS prevAVG
FROM test  AS Cur
LEFT OUTER JOIN test AS Prv
ON Cur.fscYear = Prv.fscYear + 1;

Demo

Upvotes: 0

Related Questions