asked on

SQL Select Max Date and Previous Date?

I'm having trouble selecting the Max date and Previous date. I tried the following code and it just changes the date, the price column data remains the same.

[Max Date w/ Price]
select Price, Max(date)
from table1
where Code = 4235
group by Price

--- Result ---
Price        EffDate
1234.0000      2012-07-28 00:00:00.000
1255.0000      2012-07-30 00:00:00.000

[Previous Date w/ Price]
select Price, Max(date) -1
from table1
where Code = 4235
group by Price

--- Result ---
Price        EffDate
1234.0000      2012-07-27 00:00:00.000
1255.0000      2012-07-29 00:00:00.000

[Previous Date w/ Price]
select Price, Max(date) -1
from table1
where Code = 4235
group by Price

Then I tried moving the Max Date into the Where Clause like so, but it needs a Having clause:

[Previous Date w/ Price]
select Price, Date
from table1
where Code = 4235
and Date = Max(Date) -1
group by Price

Help...

gplana

you can't put agregate functions (like max, min, sum, count, avg) on a where clause. A where clause filters original rows from the table.

What you need is to use a having clause to filter groups (having is a kind of where but after make the group by), or you can use a subquery. Try something like this:

select Price, Date
from table1
where Code = 4235
and Date = (SELECT Max(Date) -1 FROM table1)
group by Price

Surendra Nath

you need to write it as below

select Price, Date
 from table1
 where Code = 4235
 group by Price
having Date = Max(Date) -1

Open in new window

Tony303

I think MAX(Date) -1 is incorrect.

That will be the day before the last date, potentially no data.

What is needed is a Top 2 selection.
To derive the 2nd newest date...

SELECT MIN(a.Date) AS SecondNewestDate 
FROM 
(SELECT TOP 2 Date
FROM Table
WHERE Code = 4235
ORDER BY Date DESC) a

Open in new window

WorknHardr

ASKER

No matter which solution I use, doesn't work when I join tables.

ASKER CERTIFIED SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WorknHardr

ASKER

I changed the Price and Date values to a more realistic test. I just need to Join more tables to see if it still works as well.

DECLARE @T TABLE
(
Price INT, Date Datetime,code INT
)

insert into @T VALUES (300,GETDATE()-1,4235)
insert into @T VALUES (200,GETDATE()-2,4235)
insert into @T VALUES (100,GETDATE()-3,4235)
insert into @T VALUES ( 50,GETDATE()-4,4235)

-- all the above code is used to setup temporary or dummy data...

[Max Date]
SELECT Top 1 T1.* FROM @T T1
,(
select Max(Date) date
from @t
where Code = 4235
) T2
WHERE T1.code = 4235 and t1.date = t2.date

[Max Date -1]
SELECT Top 1 T1.* FROM @T T1
,(
select Max(Date) date
from @t
where Code = 4235
) T2
WHERE T1.code = 4235 and t1.date = t2.date -1

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

WorknHardr

ASKER

thank you all