Filtering dates in a group by clause

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

SQL Server 2005 Forums

Transact-SQL (2005)

Filtering dates in a group by clause

Author

Topic

erica686
Starting Member

13 Posts

Posted - 2010-01-18 : 20:53:16

I have a query:

SELECT CLIENT.ID, MAX(MATTERS.DATE_OPENED), MIN(MATTERS.DATE_OPENED)
FROM CLIENT INNER JOIN MATTERS ON CLIENT.ID = MATTERS.CLIENT_ID

I want to know if there are any matters that have a Date Opened between 19/01/09 and 19/01/10. I am having difficulty because the min and max dates might show dates outside this period, but may still contain a matter within the period. I need something like:

CASE WHEN MATTERS.DATE_OPENED >=19/01/10 AND MATTERS.DATE_OPENED <19/01/10 THEN 'Y' ELSE 'N' END

This isn't working because I have the group clause...

Any help?!?!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-01-18 : 22:01:58

You could wrap the case statement in an aggregate function, for example:

case when count(CASE WHEN MATTERS.DATE_OPENED >='19/01/10' AND MATTERS.DATE_OPENED <'19/01/10' THEN 1 end) > 0 then 'Y' else 'N' end

erica686
Starting Member

13 Posts

Posted - 2010-01-18 : 23:29:56

Thanks! very helpful!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources