[solved] Trying to obtain oldest column in a group

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

[solved] Trying to obtain oldest column in a group

Author

Topic

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2014-12-12 : 14:59:00

Hello.

I have two tables. One is a list of users:

USERS
username, userlevel
------------
John, 1
Jim, 2
John K, 3
Ben, 3
Ken, 2

The other is a list of dates:
GALLERY
crdate, creator
-----------------
2013-01-12, John
2014-12-11, Jennifer
2010-11-11, Jennifer
2011-04-03, Ben
2014-01-11, Ben

I'm trying to determine which users have the oldest crdates, that is, what users haven't added anything to the GALLERY table in a long time.

My query is:

select max(a.crdate) as last,a.creator from gallery a inner join users b on a.creator=b.username
where userlevel >1 group by creator

I use the max(crdate) to obtain the highest date from the 'gallery' table and inner join it where the creator of the gallery = the
username in the user database and has a userlevel greater than 1.

This works, and will return Ben as:
last / creator
2011-04-03 / Ben

I'm trying to find only users who have galleries older than say, 180 days. This is causing a problem.

select max(a.crdate) as last,a.creator from gallery a inner join users b on a.creator=b.username
and b.userlevel >1 where a.crdate <= GETDATE() - 180 group by a.creator

Well that works, it returns only rows older than 180 days, from users with a userlevel > 1 *BUT* it will return columns older than 180 days even if that user has a newer date.

Example: Ben will show as having a gallery for 2011-04-03 because he is userlevel>1 and the crdate is > 180 days. It ignores that Ben also has a newer column from 2014.

So I need to select crdate older than 180 days but not if there are newer crdate's for that same user.

Help?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-12-12 : 15:05:24

Use the HAVING clause.

HAVING max(a.crdate) < DATEADD(dd,-180,CAST(GETDATE() AS DATE))

HAVING CLAUSE comes after GROUP BY, but before ORDER BY.

I didn't quite follow how you get the minimum date using the MAX function. Shouldn't you be using the MIN function instead?

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2014-12-12 : 15:19:49

Thanks for the reply. If I use the MIN function then I will only see the oldest minimum dates from all users. I want to find users that have not added content within 180 days but to obtain the LATEST (MAX) date that they did contribute.

Example: You want to find which employee last used your employee benefits program, longer than 180 days ago. You'd not search the MIN date as that would be the earliest (very first time), you'd use MAX for the latest (newest) entry date he access your benefits.

The answer you provided didn't work :(, it works but doesn't provide the result.

a) older than 180 days
b) the newest date out of all the columns for that user (thus MAX)

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2014-12-12 : 15:59:35

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources