Data Occurnaces for a Day

[LeighDreyer]

I need to count the number of occurances in a day for each month ?.

I have 6 tables I use one holds 10 years of dates, the other one holds admittance dates and discharge dates for a patient. I need to calculate the occupancy each day of the month for a single month. example patients have admittance dates and discharge dates i.e admittance date 03/01/03 and discharge date 27/01/03 which means he has been in everyday between those 2 dates. I already use the Calendar table to display all occurance using Todays_date between Admit_Date and Discharge_Date but cant perfomr the count for each day of the month pleae help ??

 

[rac2]

Possibly a CASE expression will get you to the answer. The following query would get you the census on a particular day. It could be refined to count patients admitted and discharged on the same day. It would look at every patient. You could add a WHERE clause to limit it to patients not yet discharged as of the beginning of the period you need to report.

SELECT SUM(
            CASE
                WHEN Admit_Date <= '03/11/2000'
                     AND Discharge_Date > '03/11/2000'
                THEN 1
                ELSE 0
            END
           ) AS &quot;Census on March 11, 2000&quot;

FROM PatientEpisodes

The next step would be to tie that idea to a list of dates and do the query for each date. It sounds like that could be done with your calendar table. Possibly

SELECT Calendar.aDate,
       SUM(
            CASE
                WHEN Admit_Date <= Calendar.aDate
                     AND Discharge_Date > Calendar.aDate
                THEN 1
                ELSE 0
            END
           ) AS &quot;Census&quot;
FROM Calendar, PatientEpisodes
WHERE PatientEpisodes.Discharge_Date > @report_begin_date
AND Calendar.aDate BETWEEN @report_begin_date AND @report_end_date
GROUP BY Calendar.aDate

Here it would be important to limit the rows from the two tables because this is a FULL JOIN or Cartesian product of the tables. In effect every patient is associated with each date, so 100 patients served in the month is like looking at a table of 100 x 30 = 3,000 rows. The CASE statement yields 0 for any patient not in the hospital (or clinic) on that date and 1 for those who were present.

This is just an idea.