Pulling Min and Max Dates only

[brookwood]

I have a situation in my db where I get multiple datetime stamps for parameters that I have identified as the start and stop times. The frontend tool permits the users to mark activities 'done' then reopen and mark 'done' again...and again....and again.

Anywho...I need to get the minimum of the start date and the maximum of the end date.

Here's the formula that I'm using to calcualate turnaround time:

//Formula to calculate TAT excluding holidays and weekend goes in the details section for each record
WhileReadingRecords;
Local DateVar Start := Date({V_BAR_SR_ACTIVITY.ACTIVITY_CREATED_DATE});
Local DateVar End := Date({V_BAR_ACTIVITY_AUDIT_TRAIL.DATE});
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if Holidays in start to end then Hol:=Hol+1 );

If (Weeks + Days - Hol) > 1 then
Weeks + Days - Hol - 1
else if (Weeks + Days - Hol) = 1 then
Weeks + Days - Hol
else if (Weeks + Days - Hol) = 0 then
Weeks + Days - Hol + 1

What I really need is for {V_BAR_SR_ACTIVITY.ACTIVITY_CREATED_DATE} to be the minimum value from the db and for {V_BAR_ACTIVITY_AUDIT_TRAIL.DATE} to be the maximum value from the db.

It may help to know that this is my record selection formula:

not ({V_BAR_SR.CLASSIFICATION} in ["Clinical Programs", "SpecialtyRx"]) and
{V_BAR_SR.BUSINESS_UNIT} = "RxC-X-INS" and
{V_BAR_ACTIVITY_AUDIT_TRAIL.NEW_VALUE} = "Done" and
{V_BAR_ACTIVITY_AUDIT_TRAIL.FIELD} = "Status" and
{V_BAR_ACTIVITY_AUDIT_TRAIL.ACTIVITY_TYPE} = "Benefits QA Review" and
{V_BAR_ACTIVITY_AUDIT_TRAIL.DATE}={?Date} and
{V_BAR_SR.PG_CLIENT} = "Y" and
{V_BAR_SR.RELATED_TO_PROJECT} <> "Y" and
{V_BAR_SR_ACTIVITY.ACTIVITY_TYPE_CD} = "Benefits Coding"

The output that I'm getting on the report is every permutation of the TAT from the combinations of the start dates and end dates when the users marked the activities more than once. Yuck-o.

When I try using the Minimum or Maximum function in the TAT calculation, I get an error message.

I'm using CR 11.0.0.895.

How do I go about fixing this?

Thanks!!!

 

[lbass]

Wrap each date in maximum() and change the first line to "whileprintingrecords":

WhilePrintingRecords;
Local DateVar Start := Date(minimum({V_BAR_SR_ACTIVITY.ACTIVITY_CREATED_DATE}));
Local DateVar End := Date(maximum({V_BAR_ACTIVITY_AUDIT_TRAIL.DATE}));

-LB

 

[brookwood]

LB-

Thanks for the suggestion. Here's what happened:

I got the same value of TAT for each record printed to the report(meaning for example that TAT got calculated as 41 and printed to each record). And still seeing all the permutations of the service request records for each time that the users marked an activity 'done.'

How do I get the report to print the record only one time represting the minimum start time and the maximum stop time and the acurrate TAT calculation?

Thank you so much for your help. I see your name all over this site, and I know so many of us sincerely appreciate you.

-BW

 

[lbass]

Place the formula in the report header. I'm not sure what you mean about the body of the report, or what you are trying to do there.

-LB

 

[brookwood]

In the body of the report, I'm trying to see the following:

Service Request ID
Service Request Type
Service Request Start Time (minimum)
Service Request Stop Time (maximum)
TAT

So, because in my record selection formula, I've said to extract data where {V_BAR_ACTIVITY_AUDIT_TRAIL.NEW_VALUE} = "Done", I get a repetition of the reported data for each time {V_BAR_ACTIVITY_AUDIT_TRAIL.NEW_VALUE} got marked 'done'. I really just need to report the last time that {V_BAR_ACTIVITY_AUDIT_TRAIL.NEW_VALUE} = "Done" and calculate the TAT off of that value.

Thank you.

-BW

 

[lbass]

The "body" of the report doesn't tell us what report section you mean. Please explain what you are grouping on (if you are grouping), and where you are placing your fields (what section).

Are you really only trying to return one instance of your proposed results (in your last post) for the entire report?

-LB

 

[brookwood]

Ah yes, not body,...the detail section of the report.

Yes, I really only want to see one instance of my proposed results.

Thank you,

-BW

 

[lbass]

Can you clarify whether the repeating results in the detail section all have the same value?

-LB

 

[brookwood]

Yes, they do.

 

[lbass]

Then you could either format each field to "suppress if duplicated" and also format the detail section to "suppress blank section", or, you could move all fields to the report header or report footer and suppress the detail section.

-LB