Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005

    Unanswered: Counting Active Patient Days

    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 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. #2
    Join Date
    Feb 2004
    One Flump in One Place

    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

    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
    WHERE SimpleTable.DateAdmit>=DateAdd("yyyy",-1,Date())
    GROUP BY Month([DateAdmit])
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    One Flump in One Place
    Was a bit bored. I thought at first this might have been a candidate for a theta join but sadly not.

    SELECT MonthNo, YearNo, Round(Sum(IIf(Month([dateadmit])=[monthno] And Month([datedischarge])=[monthNo],DateDiff("d",[dateadmit],[datedischarge]),IIf(Month([dateadmit])=[monthno],DateDiff("d",[dateadmit],[enddate]),IIf(Month([datedischarge])=[monthno],DateDiff("d",[startdate],[datedischarge]),DateDiff("d",[startdate],[enddate]))))+1)/DateDiff("d",Max([startdate]),Max([enddate])),1) AS AvgNoPatDays
    FROM TheMonths, SimpleTable
    WHERE ((MonthNo=Month([dateadmit]) Or MonthNo=Month([datedischarge]))
     AND (YearNo=Year([dateadmit]) Or YearNo=Year([datedischarge]))) OR ((StartDate Between [dateadmit] And [datedischarge]))
    GROUP BY MonthNo, YearNo
    Right - TheMonths is a table as defined below:

    MonthNo Integer,
    YearNo Integer,
    StartDate DateTime,
    EndDate DateTime
    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.

    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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