Finding All Calls completed within 30 minutes of SLA 2

[thermalman]

Hi,
I am using Crystel Reports XI with a SQL Server 2000 backend.

I want to show All records that are Completed within 30 minutes of the Time Out (SLA)

I have tried using a Formula from FAQ but it keeps coming up with a error that the rest of the text is not part of the beginning text.

whileprintingrecords
numberVar dur:=datediff("m",{WorkOrders.TimeCompleted},{LoggedCalls.TimeOut});//get the minutes between 2 dates
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs:= Truncate(Truncate(dur/60)/60);
min:= Remainder(Truncate(dur/60),60);
sec:= Remainder (dur,60);

hhmmss:=totext(hrs,"00")+":" +totext(min,"00")+ "" + totext(sec,"00");hhmmss

I don't know if i have done this correctly but both my fields are Date/Time fields.
I am trying to to show if a call Completed Date/Time is within 30 Minutes of the expected TimeOut (SLA)

Can anybody point me in the right direction
Regards
Thermalman

 

[Madawc]

There's a much simpler method. Find the number of minutes,

Datediff ("n", {WorkOrders.TimeCompleted},{LoggedCalls.TimeOut})

Put this in a formula field and test it for being 30 or less. Maybe display it first, to check that the method is working.

Minutes are "n", "m" is months.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[thermalman]

Madawc,
Many Thanks for that, I now have the figure on the report. Is there any way drilling down to only show the records where the minutes is 30 or less but not less than 0.
at present it is showing me values with a - figure i only want to see the figure if the Call Completed was within 30 minutes of the TimeOut?

Regards
Thermalman

 

[Madawc]

You should be able to test for negatives. Or say {datetime} in [0 to 30]

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[thermalman]

Madawc,
I have tried putting what you suggested in my report but I cannot get it to show me the hours,mins difference between the TimeCompleted and the TimeOut.

My Report shows me the following

TimeIn TimeOut TimeCompleted
30/10/2006 11:00 31/10/2006 10:59 31/10/2006 09:00

My Boss wants to see the time difference between the TimeCompleted and the TimeOut i.e 1hr 59 minutes and then i need to count the number of Calls where the Time is less than 30 minutes. Is this possible and can you give me any idea how to achieve this as i have read right the way through this forum and cannot get it to do this.

Regards
Thermalman

 

[lbass]

Create a formula like this:

if Datediff ("n", {WorkOrders.TimeCompleted},{LoggedCalls.TimeOut}) < 30 then 1

Place this in the detail section and then insert a summary (a sum, NOT a count) on it. You can still use the first formula you tried to display the time as a string, but you need to use a formula like this one for calculations.

-LB

 

[thermalman]

Hi Lbass,
The formula given by Madawc is still giving me minus figures. Is is possible for the formula to show me the difference in
hours
minutes

I have read many posts on this forum and they are all like the first coding i stated in this thread. Yet when i try to add the coding into a formula the formula expert does not like it. All I want to know is what is the difference in hours and minutes between the TimeCompleted and the TimeOut

only after I have this figure do i want to count the number of calls where the datediff is less than <30 minutes

How do i go about changing the figure that i am getting now into hours and minutes?

Sorry if I am appearing a plank but i think i misunderstood madawc post.

Regards
Thermalman

 

[lbass]

I'm not familiar with your fields, but the most recent date should be the second argument in Madawc's formula, so if time completed occurs before time out, then the formula is set up correctly. This would give you positive results.

Use the following formula to display the difference:

whileprintingrecords
numberVar dur:=datediff("s",{WorkOrders.TimeCompleted},{LoggedCalls.TimeOut});//change the argument to "s"
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs:= Truncate(Truncate(dur/60)/60);
min:= Remainder(Truncate(dur/60),60);
sec:= Remainder (dur,60);

hhmmss:=totext(hrs,"00")+":" +totext(min,"00")+ ":" + totext(sec,"00");
hhmmss

Then use the formula in my earlier post to count those with duration < 30 minutes. In other words, use one formula for display and a different one to test the 30 minutes or under.

-LB

 

[thermalman]

Hi Lbass,
I created a formula called DateDiff and originally put in madawc formula into it and it gives me the seconds between the TimeCompleted and TimeOut.(both fields are SQL datetime fields)

I have tried replacing your recent post with what was in the formula but as soon as I try to save it the formula editor highlights evertything under the WhilePrintingRecords and says that the remaining text does not appear to be part of the formula?

Am i doing something wrong or do i have to put this code somewhere else?

I also got this error when putting the first lot of code in this post and only got it to work after i put in madawc formula.

Any help would be greatly appreciated

Regards
Thermalman

 

[Madawc]

If my formula gives negatives, then reverse the order of the dates. If the order varies, include a test and then use one or other formulas

As for showing it as hours and minutes, I'd do that separately. A new formula, something like

totext(truncate((@TaskTime/60))) & "hr, "
& totext(remainder(@TaskTime , 60), "00")  & " mn"

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[thermalman]

Hi madawc, That has worked a treat it will now show me in hours and minutes what the difference is. one last question

If I have the following is there a way to not count the minus values .ie.

TimeOut TimeCompleted DateDiff
11/10/2006 17:00 07/11/2006 11:30 -642.00hrs,-30mn
02/11/2006 11:44 02/11/2006 11:25 0.00hrs,19mn


Obviously we have missed this call but I only want to count records where the mn is <30 but greater than 0 (i.e. No minus values)

Can this be done?

Regards
Thermalman

 

[Madawc]

If the minutes are negative, multiply by -1 and then show it as a negative value.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10