Select Query, by max date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Query, by max date

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-09 : 06:07:01

So I have a table below

userid programmeId dateExpired

11 7 05/04/2011 09:36:11
11 22 04/02/2011 09:36:11
11 22 05/04/2011 09:36:11

Basically what I want to get max is

11 7 05/04/2011 09:36:11
11 22 05/04/2011 09:36:11

So the logic is return all rows but if a user has completed a programme already get the max date for that user.

Any Ideas ?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 06:14:33

SELECT userid, programmeId, MAX(dateExpired) AS max_dateExpired
FROM yourTable
GROUP BY userid programmeId

--------------------------
http://connectsql.blogspot.com/

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-09 : 06:18:21

Cool..Cheers

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-09 : 06:21:14

Hey just also the following

SELECT userId, programmeId, MAX(dateExpired) AS expiredDate
FROM tblUserRepeatHistory
WHERE (expiredDate <= GETDATE())
GROUP BY userId, programmeId

Because I want to checkdates in the future but I keep getting the error expiredDate is an invalid column name, any Ideas ?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 06:23:46

SELECT userId, programmeId, MAX(dateExpired) AS expiredDate
FROM tblUserRepeatHistory
GROUP BY userId, programmeId
HAVING MAX(dateExpired) <= GETDATE()

--------------------------
http://connectsql.blogspot.com/

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-09 : 06:26:42

And then very question this part
HAVING MAX(dateExpired) <= GETDATE()

is it possible to just get Date part of these 2 dates to compare and skip the times or make the time 00:00:00 or something

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 06:29:58

SELECT userId, programmeId, MAX(dateExpired) AS expiredDate
FROM tblUserRepeatHistory
GROUP BY userId, programmeId
HAVING CONVERT(VARCHAR(30),MAX(dateExpired),101) <= CONVERT(VARCHAR(30),GETDATE(),101)

--------------------------
http://connectsql.blogspot.com/

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-09 : 06:33:15

Great....Thanks for all help

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 06:34:17

my pleasure

--------------------------
http://connectsql.blogspot.com/

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-09 : 06:44:09

Sorry Just 1 Issue i think its comparing those 2 dates as Varchar and not date's if that makes sense ?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 06:48:27

don't worry, we just converted it to varchar to get date part and it will not create any problem in comparison.

--------------------------
http://connectsql.blogspot.com/

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-09 : 06:51:37

It seems like it does though for example if I run

SELECT userId, programmeId, MAX(dateExpired) AS expiredDate
FROM tblUserRepeatHistory
GROUP BY userId, programmeId
HAVING CONVERT(VARCHAR(30),MAX(dateExpired),101) <= CONVERT(VARCHAR(30),GETDATE(),101)

I get results back
11 7 05/04/2012 09:36:11
18 7 05/04/2012 09:36:11
11 22 05/04/2012 09:36:11

But since this a future date I shouldn't have got it back ?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 07:00:03

SELECT userId, programmeId, MAX(dateExpired) AS expiredDate
FROM tblUserRepeatHistory
GROUP BY userId, programmeId
HAVING CAST(CONVERT(VARCHAR(30),MAX(dateExpired),101) AS DATETIME) <= CAST(CONVERT(VARCHAR(30),GETDATE(),101) AS DATETIME)

--------------------------
http://connectsql.blogspot.com/

velnias2010
Posting Yak Master

125 Posts

Posted - 2011-05-09 : 07:05:18

Cool..Thanks again

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 07:06:25

--Best approach
SELECT userId,
programmeId,
MAX(dateExpired) AS expiredDate
FROM tblUserRepeatHistory
GROUP BY userId,
programmeId
HAVING DATEDIFF(D, MAX(dateExpired), GETDATE()) >= 0

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources