query help - querying a specific instance in time

[dundee]

I need to get a count of the number of patients admitted to the hospital for a given day for each hour during that day.
This will get my desired results for one hour but I need all 24 hours in ONE query.
(ie. SELECT count(patientID) FROM tblAdmissions WHERE admitTime < '7/4/2004 7:00 AM' and dischargeTime > '7/4/2004 7:00 AM')
Note: if a patient comes in at 7:01 and leaves at 7:59, they WILL NOT be counted...this is ok.
Thanks in advance for any help!

[skhanal]

This will give you count for each hour. If you want it to use for all dates, then you have to group by concatenating date and hour.

SELECT DATEPART(hh,admitTime),count(patientID)
FROM tblAdmissions
WHERE admitdate=givendate
GROUP BY DATEPART(hh,admitTime)

[Rawhide]

You don't have to concatenate. This query will group it by year, month, day, and hour:

SELECT DATEPART(yy,admitTime), DATEPART(mm,admitTime), DATEPART(dd,admitTime), DATEPART(hh,admitTime), count(*)
FROM tblAdmissions
GROUP BY DATEPART(yy,admitTime), DATEPART(mm,admitTime), DATEPART(dd,admitTime), DATEPART(hh,admitTime)