Reporting on Day breakdown of a month

[LeighDreyer]

Hi I have a report which I need to display a months figures but in a day breakdown i.e I have a hospital ward and they wish to know the occupancy for each day for the ward per month, they need to know how many patients came in the ward every day ! tried using days between but can manage to get individual days for each month.

 

[Subhashab]

Can u provide more info on the table and how the data is stored and a sample of the report expected output.

 

[LeighDreyer]

Hi I have created an object Occupancy that contains Patient Sex, Admission Date, Discharge Date, Ward Name. I need to display each months occupancy brokedown into days i.e say for january

Month January

Day 1 2 3 4 5 6 7 ....... 31
no.Patients 2 4 1 6 3 8 10 10
male patients 2 3 0 5 2 2 4 5
female patients 0 1 1 1 1 6 6 5

I have the a male count(object) and a female(count) Object.
There is also a parameter for the user to enter a date to start and no matter what date they enter in a month it returns the first and last dates as objects

hope this helps

cheers Leigh

 

[Subhashab]

Just clarify these points,
One the report is based on the month of the date the user enters and bring back the first day and last day of that month as objects in report.
So the days u are showing in the report is not actually present in the database but is all the days present for that month. i.e not day part of either admission date or discharge date.

and is this some thing to do with DW. b'cos i think u might need some structural change.

 

[LeighDreyer]

Hi

yes your first statement is correct

The days the patient have visited the ward are present in the database but the month days are not present in the database.

 

[jimmarkham]

I have done similar things (also for daily bed occupancy in a hospital) as follows:

Create an extra table (lets call it Dates) with one field (lets call it caldate) that is simply a list of dates. Populate it with dates that will cover everything you are looking for historically and into the reasonable future. 10000 rows covers about 30 years so it won't be big.

Then a query structure like

Select
WardName, PatientSex, caldate
from
Occupancy, Dates
where caldate between admitdate and dischargedate
....etc

will return one row for each day for each patient on the ward. Then sum them in the report or put them in a cross tab or whatever.

Hope this helps.

Jim