Solved

SQL Select Max Date and Previous Date?

Posted on 2014-01-29
8
2,368 Views
Last Modified: 2014-02-04
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...
0
Comment
Question by:WorknHardr
8 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 39818449
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
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39818669
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

0
 
LVL 12

Expert Comment

by:Tony303
ID: 39818759
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



T
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:WorknHardr
ID: 39819843
No matter which solution I use, doesn't work when I join tables.
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 250 total points
ID: 39821113
check this out

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

insert into @T VALUES (123,GETDATE(),4235)
insert into @T VALUES (124,GETDATE(),4235)
insert into @T VALUES (228,GETDATE()-1,4235)
insert into @T VALUES (223,GETDATE()-1,4235)
insert into @T VALUES (128,GETDATE()-2,4235)
insert into @T VALUES (123,GETDATE()-2,4235)

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

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

Open in new window

0
 

Author Comment

by:WorknHardr
ID: 39821378
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
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 39822035
select price, date from
(select price, date, row_number() over (partition by code order by date desc) rn
 from table1
 where code = 4235) as x
where x.rn <= 2
0
 

Author Closing Comment

by:WorknHardr
ID: 39833701
thank you all
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question