1. Registered User
Join Date
Oct 2005
Posts
1

I have a simple table which list patients, date of admit, date of discharge, and active status.

We count the following:
- Number of active patients (daily)
- Average stay of patients (average days between admit and discharge)
- Total patient days (sum of days patients are active)

My problem:

We have data where patients have been admitted for the past year. The rate of admit and discharge are not the same and not constant over the past year.

--------

I am trying to find the average daily count of active patients for the past year (monthly). A monthly summary that gives me an average...ie...50 (average number of active patients per day). From this number I can generate the total number of patient days for the month, etc...
----------

I know it is probably very easy, but I have managed to get flustered and can't see the answer very clearly anymore....Any help would be very very much appreciated.

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Hi

not so simple I'm afraid. I had a couple of cracks at it - below is the better attempt. The main difficulty is identifying the month of the stay. I have counted ALL of a patients days against the month of admit. If someone admits in Feb and stays six monthg then Febs avg is going to be artificially high and the following six months will be artificially low.

I'm afraid that sussing that final piece is the tricky bit of the query

Code:
```SELECT Month([DateAdmit]) AS Mnth, Round(Sum(DateDiff("d",[DateAdmit],[DateDischarge]))/IIf(Month([dateadmit]) In (1,3,5,7,8,10,12),31,IIf(Month([dateadmit])=2,28,30)),1) AS AvgPatDays
FROM SimpleTable

3. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Was a bit bored. I thought at first this might have been a candidate for a theta join but sadly not.

Code:
```
FROM TheMonths, SimpleTable
GROUP BY MonthNo, YearNo```
Right - TheMonths is a table as defined below:

Code:
```CREATE TABLE NewTable
(
MonthNo Integer,
YearNo Integer,
StartDate DateTime,
EndDate DateTime
)

CREATE  UNIQUE INDEX MyIndex ON NewTable (MonthNo ASC, YearNo ASC) WITH  PRIMARY```
Then enter the month number, year, startdate of the month, last date of the month.

e.g. for Jan 2005 enter:
1 2005 01/01/2005 31/01/2005

and so on for all the required months.

HTH

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
personally I'd run a separate table (say called DTStats) and run a periodic task to update the stats. The option call being whether you start with a clean DT Stats or just append changes since last update, or select a start from (prob best to run with a new DTStats and analyse the whole lot)

create in the table a <year><month><week??<NopatientsTreated> <Optionally avg no patients in month>

then divide by the appropriate number of says in the month (or days in this month upto today) in the report (the option is to do that in a query) (you will probably need a lookup table though there are some nifty date functions that could derive the number of days for you).

Persoanlly I'd prefer a look up table. I'd consider putting in a financial calendar aswell - as no doubt the beancounters will want to look at data in a financial period (say week, month, financial month(4 weeks but occasionally 5 weeks), quarter, half year etc.....)

The main reason - having captured the data (and I'm guessing it could be quite extensive you have then condensed it into a summary form - there should then be no security problems, it can be strored locally, distributed as required, reported on in any obscure way that someone wants to devise

You could possibly do the update with a (or series of) call(s) to a paramterised append query

One of the reasons being you can have confidence in the process - it may not be the most elegant solution but it does have tracability of data. The original source data and any confidentiality problems are nuked at the pass.
just my tuppenyhapoth

Posting Permissions

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