retrieve specific field from a previous date

Question

The following information is currently present in a database.

ID -- Company --- Current Value ---- Date
1 -- company1 ------- 12 --------- 25th March 2010
2 -- company1 ------- 17 --------- 25th March 2011
3 -- company2 ------- 65 --------- 23rd April 2011
4 -- company1 ------- 45 --------- 25th March 2012
5 -- company2 ------- 34 --------- 23rd April 2012

Is there some way that I can calculate the field to find the value for each company's previous year ?

ID -- Company --- Current Value --- Last Year Value ---- Date
1 -- company1 ------- 12 ---------------- 0 --------- 25th March 2010
2 -- company1 ------- 17 --------------- 12 --------- 25th March 2011
3 -- company2 ------- 65 ---------------- 0 --------- 23rd April 2011
4 -- company1 ------- 45 --------------- 17 --------- 25th March 2012
5 -- company2 ------- 34 --------------- 65 --------- 23rd April 2012

I have tried many solutions including searching this website but nothing has worked.

stringpoet stringpoet · Accepted Answer · 2012-06-12T19:26:16

Since your table appears to have dates one year apart exactly, it can be simply done by left joining itself.

SELECT a.ID, 
    a.Company, 
    a.CurrentValue, 
    ISNULL(b.CurrentValue, 0) AS 'LastYearValue',
    a.Date
FROM dbo.Table a
LEFT JOIN dbo.Table b ON a.Company = b.Company AND b.Date = DATEADD(YEAR, -1, a.Date)

retrieve specific field from a previous date

1 Answers