Are the departments inpatient, outpatient, or mixed? This kind of query gets complicated, espeically if it is for patient census reporting to federal agencies because of different reporting standards. Can you give us a bit more insight into the problem so that we can help you get the correct answer?
This is just a simple table. Department is a secondary thing and can be ignored for now if you want. The main problem is how to find the maximum number of patient that were present in a day within the given date range.
So for example:
Fred came in on 1st March, left on 9th March
Julie came in on 3 Mar, left on 4th March
Joe came in on 2nd Mar, left on 8th March.
John came in on 5th March, left on 20th march
So the maximum number of patient in the hospital in the month of March for any given day would be 3.
DECLARE @Sample TABLE
SELECT 'Fred', '20080301', '20080309' UNION ALL
SELECT 'Julie', '20080303', '20080304' UNION ALL
SELECT 'Joe', '20080302', '20080308' UNION ALL
SELECT 'John', '20080305', '20080320'
;With Yak (fromDate, toDate)
SELECT fromdate + 1,
WHERE fromdate < todate
FROM Yak AS y
INNER JOIN @Sample AS s ON s.indate <= y.fromdate
and s.outdate >= y.fromdate
group by y.fromdate
order by y.fromdate
Thanks Peso. That's what I was looking for. But there's one more thing to it...
When you run your SQL, you get Max for each day. And what I want is the max from those days. So in this case, it would be 3 as that's the max number for all days within that range.
But again, thank you so much. Its a big help.