Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Unhappy 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

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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. #3
    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. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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

    add a GROUP BY, and you're on your way

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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. #6
    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. #7
    Join Date
    Oct 2003
    Posts
    8
    Peso
    I tried doing max of count(*) in your select statement but gave me error.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please don't keep us in suspense

    what was the error?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    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. #10
    Join Date
    Oct 2003
    Posts
    8
    Thanks Peso! That's what I wanted.

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

  11. #11
    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
  •