Calculate an empty month in a few months

[jimjaix]

Sorry the title dosen't make much sense, can't come up with a good title. Here's the problem, I work in a hospital and I have a program that store patient's admit date and discharge date. My concern is, I need to calcuate patient days (the amount of days the patient stay in the hospital). Let's say a patient was admitted on Jan 15th and discharged on March 20th. So I need to build a query that capture the months, let's say I do it on Feb, how can I tell that this patient was here on Feb? since I have admit date and discharge date. Thanks


eg. we have 4 patients John Smith, Ortiz Prerez, Jim Johnson and Nancy Cruz and we are running the query for Feb.

ID Name AdmitDate Discharge Date "The result should be"
1 John Smith 1/2/07 1/26/07 24 patient days
2 Ortiz Perez 1/27/07 3/4/07 28 patient days (because this user was in the unit)
3 Jim Johnson 12/25/06 Null 28 patient days because of Feb month
4 Nancy Cruz 2/14/07 4/1/07 14 patient days

 

[CraigDolphin]

the patient days field in your query would likely take the form:

PatientDays: Datediff("d",[AdmitDate],Nz([Discharge Date],Date()))

This essentially says tell me the numbers of days between the admit date and the discharge date.....and if the discharge date is null, then tell me the numbers of days between the admit date and today.

 

[neileg]

Something like this in a calculated field:

PatientDays: Iif(IsNull([DischargeDate]), Date()-[AdmitDate], [DischargeDate]-[AdmitDate])

That will give you the patients days from the admission date to today if the patient is not yet discharged.

Edit: Craig's answer is a little more elegant than mine!

 

[jimjaix]

the patient days field in your query would likely take the form:

PatientDays: Datediff("d",[AdmitDate],Nz([Discharge Date],Date()))

This essentially says tell me the numbers of days between the admit date and the discharge date.....and if the discharge date is null, then tell me the numbers of days between the admit date and today.

I dont' want to to tell me the number of days between the admit date and today if its null, I would like to know the number of days in that month if the patient was admitted on Jan, and discharged on March (let's say we are doing Feb.)

Or let's say a patient was admited on 2/12/07 and discharged somewhere on March, the patient days for this patient should be 16 because Feb has 28 days and he was admiited on the 12, which is 16 days left for the month.

 

[Rabbie]

Then you need to use either the AdmitDate or the 1st of the month in question whichever is later as the start point and the DischargeDate or the last day of the month in question whichever is earliest as the endpoint.

BTW in your example you calculate 24 patient days for John Smith even though he was only in during January. Was that intentional or a mistake?

 

[jimjaix]

Then you need to use either the AdmitDate or the 1st of the month in question whichever is later as the start point and the DischargeDate or the last day of the month in question whichever is earliest as the endpoint.

BTW in your example you calculate 24 patient days for John Smith even though he was only in during January. Was that intentional or a mistake?

Was a mistake, really meant Feb. I am going to use the Admitdate as the 1st day of the month, and also the end I guess. so basically I am inputting the date eg 2/1/07 and 2/28/07. And the number of patients that are active in that month regardless of when they were admitted/discharged we will have a patient days for them. This is tough because I am not inputting the date under admitdate nor dischargedate.

 

[CraigDolphin]

If a patient has been in the hospital during a particular month, you want to return the number of days in that month that the patient stayed?

Is that correct?

If so, then why does John Smith return a value of 24 since he wasn't in hospital at all in february?


In any case, to return the number of days that a patient stayed in any user-specified month and year you could use the expression:

DaysThisMonth: DaysThisMonth: IIf(DateDiff("d",IIf([AdmitDate]>DateSerial(CInt([Input Year]),CInt([Input Month]),1),[AdmitDate],DateSerial(CInt([Input Year]),CInt([Input Month]),1)),IIf(Nz([Discharge Date],Date())>DateSerial(CInt([Input Year]),CInt([Input Month])+1,1)-1,DateSerial(CInt([Input Year]),CInt([Input Month])+1,1)-1,[Discharge Date])+1)<=0,0,DateDiff("d",IIf([AdmitDate]>DateSerial(CInt([Input Year]),CInt([Input Month]),1),[AdmitDate],DateSerial(CInt([Input Year]),CInt([Input Month]),1)),IIf(Nz([Discharge Date],Date())>DateSerial(CInt([Input Year]),CInt([Input Month])+1,1)-1,DateSerial(CInt([Input Year]),CInt([Input Month])+1,1)-1,[Discharge Date])+1))

Very ugly...but it works
[EDIT] Missed Rabbies and JimJaix's replies while posting...sorry

 

[jimjaix]

If a patient has been in the hospital during a particular month, you want to return the number of days in that month that the patient stayed?

Is that correct?

If so, then why does John Smith return a value of 24 since he wasn't in hospital at all in february?


In any case, to return the number of days that a patient stayed in any user-specified month and year you could use the expression:

DaysThisMonth: DaysThisMonth: IIf(DateDiff("d",IIf([AdmitDate]>DateSerial(CInt([Input Year]),CInt([Input Month]),1),[AdmitDate],DateSerial(CInt([Input Year]),CInt([Input Month]),1)),IIf(Nz([Discharge Date],Date())>DateSerial(CInt([Input Year]),CInt([Input Month])+1,1)-1,DateSerial(CInt([Input Year]),CInt([Input Month])+1,1)-1,[Discharge Date])+1)<=0,0,DateDiff("d",IIf([AdmitDate]>DateSerial(CInt([Input Year]),CInt([Input Month]),1),[AdmitDate],DateSerial(CInt([Input Year]),CInt([Input Month]),1)),IIf(Nz([Discharge Date],Date())>DateSerial(CInt([Input Year]),CInt([Input Month])+1,1)-1,DateSerial(CInt([Input Year]),CInt([Input Month])+1,1)-1,[Discharge Date])+1))


Very ugly...but it works
[EDIT] Missed Rabbies and JimJaix's replies while posting...sorry

I put that in to my expression field, its asking me for the year, month and dischargedate. I don't have a dischargedate. Not all the patient has a discharge date because they are still being treated in the unit. ITS EXTREMLY UGLY lol

 

[CraigDolphin]

Ok, it's supposed to ask you for the year and month but the discharge date is supposed to be the field in which you have the discharge date stored. I based the field names on the exmaple you showed (in which the field was [Discharge Date] with a space. If your field is named differently, then you will need to go through the expression and change all [Discharge Date] to [YourFieldNameHere] )

You could replace the [Input Year] and [Input Month] parameters to references to controls on a form or whatever.

 

[jimjaix]

Ok, it's supposed to ask you for the year and month but the discharge date is supposed to be the field in which you have the discharge date stored. I based the field names on the exmaple you showed (in which the field was [Discharge Date] with a space. If your field is named differently, then you will need to go through the expression and change all [Discharge Date] to [YourFieldNameHere] )

You could replace the [Input Year] and [Input Month] parameters to references to controls on a form or whatever.

Ya so stupid of me, anyway I did and then I am not getting the correct data. I input 2007 for year, and 1 for month (which is Jan)? I just get all the data in the database

 

[CraigDolphin]

See the query in the attached example. The expression works fine using the example data you gave. Either your input data is not as represented, or you changed something in the expression.

If you input 2007 and 1 respectively, it will show you how many january days the patient (in that record of the source table) spent in hospital.