Problem with working days calculation 1

[neilmcdonald]

Hi,

I'm using the working days and hours formula from Ken Hamady's site, but I have a slight problem: I'm using the formula to work out the response times for our customers - the formula works great for calls with one part, but for any one call there may be several parts, each part having their own completion date and time. I need to get the formula to look at the latest of these dates.

I've tried creating a running total to work out the maximum, but the report then gives no results.

Can anybody help?

Thanks,
Neil

 

[rosemaryl]

Dear Neil,

Post what you have so far and post what it is with example data you need to determine the working hours for.

For example, what indicates a different "part".

Also, what version of Crystal and against what database.

thanks,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[neilmcdonald]

Thanks Rosemary - my formula is as follows:

WhilePrintingRecords;
DateVar Start := date({SCCall.Call_InDate});
DateVar End := date({SCFSR.FSR_Complete_Date})
NumberVar Weeks;
NumberVar Days;
NumberVar Hol:= 0;

//Figure the number of Calendar "Rows" involved and count 5 days for each:
Weeks:= (Truncate (End - dayofWeek(End) + 1 - (Start - dayofWeek(Start) + 1)) /7 ) * 5;

//Adjust the first and last weeks based on when in the week you start and end
Days := DayOfWeek(End) - DayOfWeek(Start) + 1
+ (if DayOfWeek(Start) = 1 then -1 else 0) //adjust for starting on a Sunday:
+ (if DayOfWeek(End) = 7 then -1 else 0); //adjust for ending on a Saturday:

//Adjust for Holidays in the period between the start and end dates:
if Date(1999,01,01) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,01,18) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,02,16) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,05,31) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,09,06) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,11,25) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,12,24) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,01,03) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,01,17) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,02,14) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,05,29) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,09,04) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,11,23) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,12,25) in start to end then Hol:= Hol+1 else Hol:= Hol;

//Assemble the adjusted work days
Weeks + Days - Hol

The third line is the area I'm having trouble with. the report is grouped by call number so within the details section there could be several lines, each with their own completion date (({SCFSR.FSR_Complete_Date}).

An example would be a call which takes three visits to fix - there would be one ({SCCall.Call_InDate}),and three lins in the detail section, each with their own {SCFSR.FSR_Complete_Date}.

{SCCall.Call_InDate}) = 01.10.03
{SCFSR.FSR_Complete_Date} = 10.10.03
{SCFSR.FSR_Complete_Date} = 12.10.03
{SCFSR.FSR_Complete_Date} = 14.10.03

In this case I would need to know the no. of working days/hours between 01.10.03 and 14.10.03.

I hope this is clear - I'm not terribly good at explaining myself!

 

[rosemaryl]

Dear Neil,

Well, I don't think this is too bad. Place the calculation in the footer for the group for the Call number, it will naturally use the "maximum" date for {SCFSR.FSR_Complete_Date} as it will use the last record's data for anything in the group footer.

Try that and see if you get the results you want.

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[neilmcdonald]

Thanks Rosemary - That works perfectly! I guess it's not so complicated when you know what you're doing!