SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Filtering dates in a group by clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

erica686
Starting Member

13 Posts

Posted - 01/18/2010 :  20:53:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/18/2010 :  22:01:58  Show Profile  Reply with Quote
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
Go to Top of Page

erica686
Starting Member

13 Posts

Posted - 01/18/2010 :  23:29:56  Show Profile  Reply with Quote
Thanks! very helpful!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000