Building SQL Query

Question

Hi all, I need some help to build SQL Query. I have table having data like:

ID   Date         Value
 1   12/01/2009     4
 2   12/02/2009     3
 3   12/03/2009     6
 4   12/01/2008     2
 5   12/02/2008     4
 6   12/03/2008     5

Here is my query:

select ID, Date, Value from MyTable
where Date between '12/01/2009' and '12/04/2009'

and it returns the following rows:

ID   Date         Value
 1   12/01/2009     4
 2   12/02/2009     3
 3   12/03/2009     6

But I need the data in the following format:

ID   Date         Value   lastYearValueANDSameDate
 1   12/01/2009     4                2
 2   12/02/2009     3                4
 3   12/03/2009     6                8

Here I need to get the last year value of the same date in the lastYearValueANDSameDate column.

Thanks

Answer 1

see this answer. it's the same problem in a different database. adapt to your situation.

Answer 2

Have a look at this

DECLARE @Table TABLE(
     ID INT,
     Date DATETIME,
     Value INT
)

INSERT INTO @Table SELECT 1, '12/01/2009', 4
INSERT INTO @Table SELECT 2, '12/02/2009', 3
INSERT INTO @Table SELECT 3, '12/03/2009', 6
INSERT INTO @Table SELECT 4, '12/01/2008', 2
INSERT INTO @Table SELECT 5, '12/02/2008', 4
INSERT INTO @Table SELECT 6, '12/03/2008', 5

SELECT  t.ID,
     t.Date,
     t.Value,
     tPrev.Value
FROM    @Table t LEFT JOIN
     @Table tPrev ON DATEADD(yy, -1, t.Date) = tPrev.Date
WHERE   t.Date between '12/01/2009' AND '12/04/2009'

Answer 3

Untested but I think it will work:

select a.id, a.date, a.value, b.value
from mytable a, mytable b
where a.date >= '12/01/2009'
and   a.date <= '12/04/2009'
and   a.date = dateadd(year,1,b.date)

Keep in mind that this is a Cartesian product (albeit not a full cross-join due to the last and clause) so it will not produce rows for dates that do not have corresponding last-year rows.

Answer 4

DECLARE @Table TABLE(
ID INT,
Date DATETIME,
Value INT
)

INSERT INTO @Table SELECT 1, '12/01/2009', 4
INSERT INTO @Table SELECT 2, '12/02/2009', 3
INSERT INTO @Table SELECT 3, '12/03/2009', 6
INSERT INTO @Table SELECT 4, '12/01/2008', 2
INSERT INTO @Table SELECT 5, '12/02/2008', 4
INSERT INTO @Table SELECT 6, '12/03/2008', 5


SELECT *,(SELECT TOP 1 Value FROM @Table WHERE Date=DATEADD(YEAR,-1, a.Date)) AS last FROM @Table a

WHERE a.Date between '12/01/2009' AND '12/04/2009'