# Thread: Date range query - complex one

1. Registered User
Join Date
Oct 2003
Posts
8

## Unanswered: Date range query - complex one

I have a table which records patient number, department they admitted in, the admit date and relieve date.

I need to find out during a given date range what was maximum number of patients in a day in a department.

So for example, during the month of march, which day had the maximum number of patients and the number of patients in a department.

Table1

patient_ID Department Date_In Date_out

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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?

-PatP

3. Registered User
Join Date
Oct 2003
Posts
8
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.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
if you are looking to find the number of patients present each day of a month, and then find the maximum of that, and you're hoping that it might be simpler in SQL than in an application program...

... it might be

let's start off with a table of numbers

CREATE TABLE numbers (n INTEGER NOT NULL PRIMARY KEY);

now populate this table with the numbers from 0 through 30

now write a query against this table such as
Code:
```SELECT DATEADD(day,n,'2008-03-01') AS theday
FROM numbers```
this will generate each date in the month (you can easily add some WHERE condition to handle stuff like February, right?)

okay, now you have each date in the month, use this as the left table in a LEFT OUTER JOIN and join to every row in your table where theday is between the admit and relieve dates of the patients

5. Registered User
Join Date
Apr 2007
Posts
183
Code:
```DECLARE	@Sample TABLE
(
pat VARCHAR(20),
indate DATETIME,
outdate DATETIME
)

INSERT	@Sample
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)
AS (
SELECT	MIN(indate),
MAX(outdate)
FROM	@Sample

UNION ALL

SELECT	fromdate + 1,
todate
FROM	Yak
WHERE	fromdate < todate
)

SELECT		y.fromdate,
count(*)
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```

6. Registered User
Join Date
Oct 2003
Posts
8
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.

7. Registered User
Join Date
Oct 2003
Posts
8
Peso
I tried doing max of count(*) in your select statement but gave me error.

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
please don't keep us in suspense

what was the error?

9. Registered User
Join Date
Apr 2007
Posts
183
Code:
```SELECT	TOP 1	y.fromdate,
count(*)
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	count(*) DESC```

10. Registered User
Join Date
Oct 2003
Posts
8
Thanks Peso! That's what I wanted.

R397, there was a syntax error putting max on count(*).

11. Registered User
Join Date
Apr 2007
Posts
183
If you want all dates with the maximum count, use TOP 1 WITH TIES

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•