Building SQL Query

Question

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

see this answer. it's the same problem in a different database. adapt to your situation. — just somebody
– just somebody, Commented Dec 24, 2009 at 5:58
The requirements listed are not quite complete, and the sample data set does not answer the unknown questions. It appears you want entries of lowest ID joined with entries of next higher ID on the right. Is that correct? — Nick Larsen
– Nick Larsen, Commented Dec 24, 2009 at 5:58
@NickLarson; plz check now, I have edit my question. have you got my requiment now? — Muhammad Akhtar
– Muhammad Akhtar, Commented Dec 24, 2009 at 6:02
@Muhummad, I cleaned up the formatting and added (what I think is) your full query (always a good idea including the full one since there may be other clauses in there which change behaviour). Please check that the query is correct and fix if not. — paxdiablo
– paxdiablo, Commented Dec 24, 2009 at 6:40
@Paxdiable; yes, right, the issue is resolved by Astander answer, I also try your answer, it gives me the result but not 100% and Astander answer give me 100% result what I want.. Thanks for help.. — Muhammad Akhtar
– Muhammad Akhtar, Commented Dec 24, 2009 at 6:52

Adriaan Stander · Accepted Answer · 2009-12-24 06:08:36Z

1

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'

answered Dec 24, 2009 at 6:08

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Muhammad Akhtar Over a year ago

@Astander; That's grate answer, I hope this will work for me, let me check first. Thanks

Muhammad Akhtar Over a year ago

@Astander, I send you an email, I need some help in query. Plz reply. Thanks

Adriaan Stander Over a year ago

Have you placed the query in SO yet, I will have a look when I can ASAP X-)

paxdiablo · Accepted Answer · 2009-12-24 06:12:03Z

1

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.

answered Dec 24, 2009 at 6:12

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

Comments

surajit khamrai · Accepted Answer · 2009-12-24 06:35:17Z

1

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'

answered Dec 24, 2009 at 6:35

surajit khamrai

Collectives™ on Stack Overflow

Building SQL Query

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related