Stumped

[scdmh]

This has been driving me crazy for a long time now andit keeps cropping up. I need to find out how many patients were in the hospital between two dates.

I need to set up a crosstab report based on months:
Jan Feb Mar Apr
Hospital A
Hospital B

I have an admission date and a discharge date. I need to test whether the Month/year in the crosstab is between the admission and discharge dates. If so then I want to count that patients. I have racked my brain and I can't figure this one out. I hope someone has a solution for me.

Thanks,
Dianne

 

[fisheromacse]

sounds like you need to set up a test for the date for each month.
Something like this.
//{@Jan}
numbervar ja;
IF (month({table.AdmDate})=1 or month({table.DisDate})=1) then ja := ja +1 else ja := ja;

 

[IanWaterman]

The easiest way to do this is in the database. If you are using SQL Server there is a PIVOT function which will allow you to pivot data against a dummy Month table.

You could then report off a command using such a query.

Ian

 

[scdmh]

I'm not sure. Let's say I have a record where the admission date = 12/1/1995 and the discharge date = 2/15/1996 (this is a long-term hospital). My crosstab dates are, for each month, 7/1/1995 through 6/30/1996.

The crosstab would indicate "1" for Dec, Jan, and Feb. The other months would be "0".

 

[lbass]

I would use conditional formulas like this:

//{@Jan}:
if {table.admissiondate} <= date({?year},1,31) and
(
isnull({table.dishargedate}) or
{table.dischargedate} >= date({?year},1,1)
) then
{table.patientID} else
tonumber({@null})

...where {@null} is a new formula that you open and save without entering anything.

Insert a group on hospital and then insert a distinctcount on formulas like the above if you want the patient counted only once per month (even if multiple submissions) or you could insert a count if you wish (as long as data isn't duplicated). Remove the tonumber() is patientID is a string.

Then suppress the detail section and GH and drag the groupname into the group footer.

-LB

 

[lbass]

PS. In the future, please try to use thread titles that identify the issue so that the topic can be easily searched.

-LB

 

[scdmh]

Thanks. This really helps.
Dianne