DateDiff formula is counting the weekends and it should not be..

[OKCMikeSoll]

I am using CR 9 reporting on an oracle database. I have a report that is calculating the average open time of helpdesk tickets that are in an open status. Listed below are the formulas I am using and they are giving me the correct average age but the average age includes the 48 hours from the weekend, which I was told my @TimeDiff formula would backout. The only difference is that in the @TimeDiff formula I changed it from the "d" to the "s" beucase I need the difference in seconds. Any thoughts?

//@TimeDiff
//I use this to give me the number of seconds between to dates and backout the weekends.
DateDiff("s",{@CST_OpenTime},{@DateVar}) -
DateDiff("ww",{@CST_OpenTime},{@DateVar},crsaturday) -
DateDiff("ww",{@CST_OpenTime},{@DateVar},crsunday);

//@DateVar (This gives me a "stop time" to use in the @TimeDiff formula
DateVar abc:=CurrentDate-1;
NumberVar mon:=Month(abc);
NumberVar dy:=Day(abc);
NumberVar yr:=Year(abc);
DateTime(yr,mon,dy,23,59,59)

//@TimeDiffGroup - formula gives me the average for all the tickets in seconds then converts to days:hours:mins:secs

Local NumberVar TotalSec := Average ({@TimeDiff},{PROBSUMMARYM1.ASSIGNMENT}) ;
Local NumberVar Days := Truncate (TotalSec / 86400);
Local NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
Local NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
Local NumberVar Seconds := Remainder ( TotalSec , 60);

Totext ( Days, '00', 0,'') + ':'+
Totext ( Hours, '00', 0,'') + ':'+
Totext ( Minutes,'00', 0,'') + ':'+
Totext ( Seconds,'00', 0,'')

Thanks in advance..

Mike Soll

 

[synapsevampire]

If you know that it worked, chahnge it back to d and then change it to seconds afterwards. There are many approaches to determining business periods, I opt for table driven means, but there are plent7y of Crystal based solutions.

Check out Ken Hamady's formula page:

http://www.kenhamady.com/formula.html

-k

 

[OKCMikeSoll]

synapse-

I can get more accurate results if I get the datediff in seconds and then convert it to dd:hh:mm:ss. If I use datediff in days then convert to seconds it will could possibly be way off. Do you have any ideas on this? Or, Can you divuldge a little further on your "table driven means".

Thanks,
MikeSoll

 

[synapsevampire]

You're difficulty is that you're subtracting 2 days from a seconds count, this should work, I'm not sure as I've no idea what's in your formulas:

DateDiff("s",{@CST_OpenTime},{@DateVar}) -
(DateDiff("ww",{@CST_OpenTime},{@DateVar},crsaturday)*86400) -
(DateDiff("ww",{@CST_OpenTime},{@DateVar},crsunday)*86400);

Change this to either subtract the number of seconds for the weekend period (multiply by , or go to days, or use a period table to provide the number of business hours in a period as data warehouses do.

-k