Formula help needed - distinct value needed

[cyreports]

In my report I have 4 Grouping Levels.

Group 1 - Agency
Group 2 - PatientName
Group 3 - CaseType
Group 4 - Status

In the report I am pulling in two date fields, a StartDate and an EndDate. I have also created a SQL calculation to give me the Number of days between the StartDate and EndDate.

Here is the issue I am facing, a Patient will have either a Medical or a Behavioral Health CaseType assigned to them for the visit. The issue is they can have both types of Cases on the same day. They can come in for a Medical reason and for a Behavioral Health related issue.

My issue is I need to count the number of distinct days and when I get the count on the Group Level for the Patient (Group2) my values get off due to visits with two case types in the same day. I need help getting this organized and set up right. Any help is appreciated.

 

[Madawc]

If you also grouped by Day and set your totals to update at day-group level, that should fix it. It would need a running total, or a variable.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.


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

 

[cyreports]

Each line will have a date span from StartDate to EndDate .. so one could have a datespan like:

07/15/2009 - 07/18/2009

This would give me 4 days - (07/15, 07/16, 07/17 and 07/18). I can not group on Day and thats the issue.

 

[lbass]

You haven't explained clearly what count you are expecting if there are visits to each case type on the same day. Let's say there is a medical visit on June 23 lasting until June 27, and that there is also a behavioral health visit on June 23 ending the same day. What result would you want to see? What happens if the behavioral health visit is on another day within the range?

-LB

 

[cyreports]

I want a distinct count of the number of days regardless of the case type. Like i mentioned a patient can have both case types on a given day:

Example:

Medical

July 15 - July 18

This would be 4 days (July 15, July 16, July 17 and July 18)

Behavioral Health

July 16 - July 17

This would be 2 days (July 16 & July 17).

Because the 16th and 17th overlapped each other the total count of days is 4. I need that distinct count on the PatientName Grouping.

Please note the data is entered with a date span StartDate and EndDate.

So if we took the example I used, in my SQL db, I would see 2 rows of data. One row for the Medical services with StartDate = July 15 and EndDate = July 18. I would then get a row for Behavioral Health with a StartDate of July 16 and an EndDate of July 17. I have a DATEDIFF calculation in place already giving me a count of Days.

Somehow, I need to account for the true number of days and right now (using my same example) I am getting a Total number of days = 6 when in fact it needs to show 4. That explain this any better?

 

[Madawc]

If all records for a single day were grouped, you could get the totals without counting a day twice. If necessary this could be in a subreport.

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

 

[N22S]

This might work -

Create four different variable formulas.

The first is to reset the variable and is placed in the patient header

whileprintingrecords;
datetimevar minstart := CDateTime (2100, 01, 11, 00, 00, 00);
datetimevar maxend := CDateTime (1900, 01, 11, 00, 00, 00);

The second is to calculate the minimum start date for the patient and is placed wherever the dates are "displayed":

whileprintingrecords;

datetimevar minstart;

if minstart > {start.date} then minstart:={start.date} else
minstart:= minstart;

The third is to calculate the maximum end date for the patient and is placed wherever the dates are "displayed":

whileprintingrecords;

datetimevar maxend;

if maxend < {start.date} then maxend:={start.date} else
maxend:= maxend;

The last is placed in the patient group footer and calculates the difference.

whileprintingrecords;

datetimevar maxend - datetimevar minstart;

You'll have to do some conditional formatting if the dates do NOT overlap, along the lines of the max and min variables being valued differently if both the end and start date fields are both either before or after the max and min variables. I'd work on it some more but I have to attend to other business!

 

[cyreports]

All -

This is what I ended up using, although I am still needing help to get a total on my Group 1 and Report total. How would I set up these formulas to give me accurate totals?

In the patient group header:

//set up array and reset  for each patient
whileprintingrecords;
Numbervar array days;
redim days[1000];
local numbervar x;
for x :=1 to 1000 do
days[x]:=0;
""

In the detail line.

whileprintingrecords;
Numbervar array days;
local numbervar x;
local datevar arraystart;
local datevar rangestart;
local datevar rangeend;
local numbervar S;
local numbervar E;
//set array values to 1 for each date in range
//set start date of array to start of last year
ArrayStart:=dateserial(year(currentdate)-1,01,01);

rangestart :=datevalue({ado.StartDate});  
rangeend:=datevalue({ado.EndDate});

s:= rangestart-arraystart+1;
e:=rangeend-arraystart+1;

for x :=s to e do
days[x]:=1;
""

In the Patient group footer:

whileprintingrecords;
Numbervar array days;
local numbervar x;

local numbervar totdays;

//sum array values to get span of days

for x :=1 to 1000 do
totdays:=totdays+days[x];
totdays

 

[cyreports]

madawc -

I did some SQL programming and got a single day returned for each day in the span. I can not quite seem to get it to still give me totals and display the report right. My new field name is SINGLEDATE. Can you elaborate more on your suggestion? I added in SINGLEDATE as GROUP 4 and I dont quite think thats right. I just want the sum of the SINGLEDATE and not sure what to do.

 

[Madawc]

In SQL, I usually get totals by grouping in a fresh table. This may not be the best method, but it does the job.

There's also a separate SQL forum if you need help with coding.

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