Working out daily counts on values within a date range

[ap22]

Hi

Staff here would like to be able to work out the daily count of patients in different wards (Emergency and Surgical ward) over a specified time period according to their ward and severity. The fields I have to work on are ward admission and ward discharge date, ward name, severity level and Patient ID.
Eg.
Patient ID Adm Date Disch Date Ward Severity Level
1 13/8/03 16/08/03 ED High
2 14/08/03 15/08/03 ED High
3 12/08/03 12/08/03 Surg Med
4 15/08/03 27/08/03 ED Med

The result would be like this for the user specified date range of 12/08/03 to 16/08/03.
Ward Severity 12/08/03 13/08/03 14/08/03 15/08/03 16/08/03
ED High 0 1 2 2 1
ED Med 0 0 0 1 1
Surg High 1 0 0 0 0

I am not sure how to count day by day when I only have the admission and discharge date.

Any assistance would be greatly appreciated.

Thanks

 

[MichaelRed]

If the report is for the specific period (per example) first restrict the date range to those dates. In a "report", you can group by the several categories and report on each with individual records. By including the 'running sum' / count in each group (and saving the values), you could generate a report footer of the format indicated.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ap22]

Hi

I was looking for a result more in terms of a crosstab query. I can easily count the patients on the admission and discharge days but I need to count their presence on the days in between. ie. Admission 12/08/03 and Discharge 14/08/03 but how do I count them into the daily total on the 13/08/03. I have been asked for day by day data for each ward for one year so their are thousands of records.

Thanks
ap22

 

[oharab]

You will need to generate your query something like:

SELECT
Ward,
SUM(if(and(startdate<=#9/1/3#,or(enddate>=#9/1/3#,isnull(enddate),1,0)) AS 01Sept,
SUM(if(and(startdate<=#9/2/3#,or(enddate>=#9/2/3#,isnull(enddate),1,0)) AS 02Sept,....

FROM
tblPatients
GROUP BY
Ward

You will need some code to generate this query according to required start and end dates.

Let me know if you need more.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[MichaelRed]

For an Xtab, it is somewhat less than straight forward.

My version of the patients table:
PatId AdmDt DisDt Ward Sever
1 8/13/2003 8/16/2003 ED High
2 8/14/2003 8/15/2003 ED High
3 8/12/2003 8/12/2003 Surg Med
4 8/15/2003 8/27/2003 Ed Med

A table of inclusive Dates to Report on:
MyDt
8/10/2003
8/11/2003
8/12/2003
8/13/2003
8/14/2003
8/15/2003
8/16/2003

The &quot;Source&quot; recordset for the Xtab:[/b]
PatId AdmDt DisDt[tab]Ward[tab]Sever[tab]MyDt
1[tab]8/13/2003 8/16/2003[tab]ED[tab]High[tab]8/13/2003
1[tab]8/13/2003 8/16/2003[tab]ED[tab]High[tab]8/14/2003
1[tab]8/13/2003 8/16/2003[tab]ED[tab]High[tab]8/15/2003
1[tab]8/13/2003 8/16/2003[tab]ED[tab]High[tab]8/16/2003
2[tab]8/14/2003 8/15/2003[tab]ED[tab]High[tab]8/14/2003
2[tab]8/14/2003 8/15/2003[tab]ED[tab]High[tab]8/15/2003
3[tab]8/12/2003 8/12/2003[tab]Surg[tab]Med[tab]8/12/2003
4[tab]8/15/2003 8/27/2003[tab]Ed[tab]Med[tab]8/15/2003
4[tab]8/15/2003 8/27/2003[tab]Ed[tab]Med[tab]8/16/2003

The SQL to generate the above recordset:
SELECT tblPatient.PatId, tblPatient.AdmDt, tblPatient.DisDt, tblPatient.Ward, tblPatient.Sever, tblDts.MyDt
FROM tblPatient, tblDts
WHERE (((tblDts.MyDt)>=[AdmDt] And (tblDts.MyDt)<=([DisDt])))
ORDER BY tblPatient.PatId, tblDts.MyDt
WITH OWNERACCESS OPTION;

PLEASE NOTE!!! There is NO join clause in this query. It simply &quot;HAS&quot; the second table as a reference for the limits!!!!!


The Results:
Ward Sever Total Of PatId 8/12/03 8/13/03 8/14/03 8/15/03 8/16/03
ED High 6 1 2 2 1
Ed Med 2 1 1
Surg Med 1 1


Sorry about the 'formatting', Tek-Tips doesn't really suport the &quot;wide&quot; query results.

Also, I have not presented a programatic method to generate the recordset of the dates to include. While there are numerous ways to do so, I was only responding to the Xtab request. A simplistic approach would be to simply have a date list table and use some parameters in a query to limit the dates in the recordset to those of interest. A minor issue with this would be to assure that the table of dates was ALWAYS inclusive of the possible dates of interest.

One further note, is that Ms. A. always requires dates in queries to be in the &quot;standard&quot; (aka U.S. Format of &quot;m/d/y&quot;, with the only variation permitted being the length of the individual date part specifiers). As can be seen, my &quot;test&quot; set is in this format.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ap22]

Hi

Thanks for the responses. I used MichaelRed's answer and it worked great. I didn't put the dates in US format and it still worked fine.

Much appreciated
ap22