Help with a complex Running Total

[cyreports]

I need some assistance with a running total / which may involve the use of a formula. Just a little background on the issue.

A patient can present to the clinic for one of two case types: Medical or BHS. (BHS = Behavioral Health). A patient can also be treated by both case types on a given day. What I need to do is return a total number of days a patient was off sick for both case types and count the day as one day when the case types overlap.

Example Patient is scheduled off 06/21/2010 and 06/22/2010 for CaseType BHS. The patient is also scheduled off 06/21/2010 - 06/24/2010 for Medical.

My SQL would return 6 rows. 2 rows for the BHS and 4 rows for the Medical. Because 06/21/2010 and 06/22/2010 overlapped each other with both case types, I would expect to only see 4 days total off for this patient.

The fields involved for this calculation are as follows:

NumberOfDays
CaseType (Returns either the Medical or BHS)
ado.PFCDutyStatusID (Returns a patients duty status for the given case type - Sick, Limited or Full).
SINGLEDATE (Returns the individual day)

Right now I have a running total named "Days Total" thats set up as such:

Field to Summarize: ado.NumberOfDays
Type of Summary: Sum
Evaluate on change of field: ado.PFCDutyStatusID
Reset: Never

If I did not explain well enough or you have additional questions, please ask, I will try my best to answer. Thank you in advance for any help you can offer me.

 

[lbass]

If your data returns one row per date, then I think you can simply insert a distinctcount on the date field per patient.

-LB

 

[cyreports]

lbass,

I must be doing something wrong. I have 4 grouping levels in my report.

ado.GroupBy1
ado.GroupBy2
ado.GroupBy3
ado.SINGLEDATE

I tried adding in ado.PatientName after the ado.GroupBy3 and added in a runningtotal named PatientSum.

Field to summarize = ado.SINGLEDATE
Type of Summary = Distinct Count
and Reset on change of Field = ado.PatientProfileId

I am missing something because my totals are the same in the groupings. I would love to return this once per patient and not after all the other groupings.

 

[lbass]

Please identify what fields you are grouping on. "GroupBy1", etc. doesn't explain anything.

-LB

 

[cyreports]

LBass-

ado.GroupBy1
ado.GroupBy2
ado.GroupBy3

These can be multiple things. It is driven by a CASE SQL Statment. The options the user has are:

Case Type
Diagnosis
Status
POD (this is an internal classification for a patient).

 

[lbass]

If you want the number of days per patient, then insert a group on the patient field and make it GH#1. Then you can insert a distinctcount on the date field at the patient level.

-LB

 

[cyreports]

LBass -

That worked like a charm. A follow-up question to this would be, can I take that distinct count from the Patient level and report back all patients distinct counts in my report footer?

 

[lbass]

You could insert a crosstab that uses patient as the row and distinctcount of date as the summary field.

-LB