Maximum count during year

[BigDaddyE]

I need to list the highest patient count during last year. Each patient {Patient.PatientID}, has a start date {Patient.StartDate} and an end date {Patient.EndDate}. I am calculating if a patient is active during the year with the following:

{Patient.StartDate} <= {?End Date} // 12-31/2010
and (IsNull ({Patient.EndDate})
or
{Patient.EndDate} >= {?Start Date}) // 01/01/2010

I would hate to run this for each day of the year. So, I am looking for a possible Cross Tab that shows each day of the year and how many patients were active on that day. Then I can show the entire year and select the Maximum and Minimum Patients that were active during the year. I could also see the dates that the Max and MIN occured on.

Help Please

 

[Madawc]

I think this goes beyond what Crystal is designed to do. Crystal has a 'flow', a fixed cycle that you have to slot your own code into. It isn't a full programing language: that's the price you pay for a software tool that can be used to produced a nice-looking report very quickly.

ou could (with a lot of work) create separate counts for each day of the year and then count the number of patients who were active for that particular day. Or if you can get the database changed, have a table added with an entry for each day of the year, but I don't know of a way to link those records to patients active that day.

If you have SQL, you might ceated an SQL Command that would ceater an entry for each patient active on a particular day of the year. This could then be used as if it were a Crystal table and you could get the maximum value using TopN. I think you'd need to duplicate this in a subreport to get the minimum value.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

You can do this by grouping on date (on change of date). I don't think you really should include every date in the year, since then your minimum would always be zero (because of dates with no patients.

After grouping, create a formula to be placed in the date group header and suppressed:

whileprintingrecords;
numbervar maxcnt;
datevar maxdt;
numbervar mincnt;
datevar mindt;
if count({table.patientID},{table.date}) > maxcnt then (
maxcnt := count({table.patientID},{table.date});
maxdt := {table.date}
);
if groupnumber = 1 or
count({table.patientID},{table.date}) < mincnt then (
mincnt := count({table.patientID},{table.date});
mindt := {table.date}
);

In the report footer, use a formula like this to display the results:

whileprintingrecords;
numbervar maxcnt;
datevar maxdt;
numbervar mincnt;
datevar mindt;
"Maximum Patient Count on "+totext(maxdt,"MM/dd/yyyy")+": "+
totext(maxcnt,0)+chr(13)+
"Minimum Patient Count on "+totext(mindt,"MM/dd/yyyy")+": "+
totext(mincnt,0)

Format the result to "can grow".

-LB

 

[BigDaddyE]

I am wondering how to figure out what {table.date} would be. I only have the {Patient.StartDate} and {Patient.EndDate}..... So every date between and including the two dates are what I am assuming {table.date} would be. I just do not know how to do that.

 

[lbass]

Sorry, I didn't notice the start and end date distinction. You would have to create a table that contains all possible dates, e.g., in Excel. Then you would add this table to the database expert and link it with an inner join to the table containing your dates. Link {xl.date} with a >= join link type to the start date and with a <= join link type to the end date. Ignore the warning message about two data sources. This will cause one record to be created per day between start and end date. Then the above formula should work. Your date group would be on {xl.date} and use that also in the formula. (Or whatever the Excel date is called--I'm just calling it xl.date.) You can easily create the Excel spreadsheet by adding a date to the first cell and then using the "fill" function to create the rest of the dates in the column.

-LB