bertj bertj - 6 months ago 10
SQL Question

SQL where or having in

I would like to keep this in one query if possible. basically, I want to get the ID of anyone seen in the last 5 days or have been seen more than twice and keep it in the same query. If splitting it into a couple sub queries or temp tables is the best route to go then I can go that route. I just want to keep it as condensed as possible. Any suggestions would be greatly appreciated.

select ID,Name
from some table a
where cast(a.[appointment date] as date) > cast(GETDATE() - 5 as date)
group by id,name
or having count(id) > 2

Answer

Move the date condition to the HAVING clause:

select ID, Name
from sometable a
group by id, name
having count(id) > 2
    or max(cast(a.[appointment date] as date)) > cast(GETDATE() - 5 as date)