Need to calculate business time removing pending time

[ITCPhil]

Hi,

This is on Crystal Report 10 connecting to Oracle. It is something, if even possible that is well beyond my knowledge of crystal. I would like to know if it is something that is doable or if I am wasting my time looking at it.

Basically, our incidents are recorded into a database. Now, my client wants to get the resolution time of a ticket but wants it done for business hours only AND excluding the time when an incident is in pending. The business hours I have working properly, but the pending I don't know if it is possible.

I won't paste code as I don't have any for this. The thing to know is that when an incident goes in pending multiple times, only the last time is kept in the view. It discards everything else so I cannot use a status history view to get data.

Instead, to get this I would have to figure out a way to search a CLOB field for specific lines of text and then print the line over it, which shows the date. Then, this date would need to be converted from a string to a time in seconds where I would substract the start time from the end time.

In short, this is what it looks like :
20/03/2006 3:14:00 PM Chabot Marc
The ticket is now in Pending-Vendor mode, until 21/03/2006 9:13:21 AM ATL.

21/03/2006 9:22:03 AM AR_ESCALATOR
BF Date has expired on ticket # 1559382, an alert has been sent to something@something.com

I would need to search for 'The ticket is now in Pending' and 'BF Date has expired on ticket', get the line above and convert the caracters to seconds. I would then get the total number of seconds, substract it from my total number of seconds in business time and convert this to the actual time to resolution.

The issues are as follows :
- We print PM and AM instead of using a 24 hour clock.
- Someone can manually remove a ticket from pending

So, is this something that I even want to try working on or should I just tell her straight away it is not possible?

I leave in minutes so will not post back until Monday if required.

Thank you,
Phil

 

[Madawc]

It's not clear to me how you recognise a ticket as pending. You can use Record Selection to exclude some records. Or group multiple transactions for the same account (or ticket) and find the minimum or maximum.

There are also running totals, which can be set to accumulate according to a rule rather than by group.

Does this help?

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

 

[ITCPhil]

Not really Madawc, a pending ticket is usually set that way because they are waiting on something. It can be removed manually or automaticall from pending and placed in pending multiple times over its life cycle.

The status history we use however does not allow for multiple pendings, it just shows the last datetime when a status is changed so if a ticket is placed to a status multiple times, it is just thrown out except the last time.

I think I will tell her it is not something I want to get into, it would be too problematic if possible.

Thanks,
Phil

 

[lbass]

You could probably do this if you can use the CLOB field in a formula--and I'm unclear on that. Have you been able to use this field in a formula? Also, it's unclear when you refer to "print the line over it"--is this a separate detail or in the same detail instance?

-LB

 

[ITCPhil]

Well, I can see my CLOB fields in the formula editor so it looks like it might be possible. I have however never heard of doing this, is there an easy way of going through it?

I probably should not say to print the line, more of it looking at the line and making a string out of it, basically, when I look at :

20/03/2006 3:14:00 PM Chabot Marc
The ticket is now in Pending-Vendor mode, until 21/03/2006 9:13:21 AM ATL.

I want Crystal to make a string of '20/03/2006 3:14:00 PM' and turn it into a datetime at which point I can convert this to seconds.

Thanks,
Phil

 

[lbass]

My question is whether your example is all from the same detail record or from two separate records.

-LB

 

[ITCPhil]

Oh, sorry. It is from the same detail record. This is what it looks like in a ticket with a very long life cycle (I changed the names and removed the french to reduce the size). You see how people change the pending date (BF date changed) and it does not always come out of pending (BF expired) as some might reassign it directly from pending.



09/05/2005 10:02:32 AM Helpdesk
An email notification has been sent to the client for the assignment of his/her Service Request.

An email notification to Resolver Group for the assignment of the Service Request has been sent to NC-SYS-DIST-NCCPM-GPNCA-R3

The problem has been assigned to NCCPM-GPNCA.R3.
The System / Application value in the Items Affected menu group is :QCAP002 (MONTREAL SYMPOSIUM)
The System / Application value in the Cause Code menu group is : QCAP002 (MONTREAL SYMPOSIUM)
The Problem's Severity is 2

09/05/2005 10:13:11 AM Helpesk
A Severity 2 notification email has been sent to Support

09/05/2005 10:28:16 AM Support
The problem is now work in progress .

09/05/2005 11:48:05 AM Support
The ticket is now in Pending-Support mode, until 10/05/2005 11:43:54 AM ATL.

Escalation Delay Timer has been stopped.

10/05/2005 9:52:05 AM Helpdesk
An email notification for a Service Request update has been sent to NC-SYS-DIST-NCCPM-GPNCA-R3

10/05/2005 11:44:34 AM AR_ESCALATOR
BF Date has expired on ticket # 1292738, an alert has been sent to the Service Desk Duty Officer.

10/05/2005 11:44:40 AM Helpdesk
A BF Date Expiry notification email has been sent to NC-SYS-DIST-NCCPM-GPNCA-R3

10/05/2005 1:03:48 PM Support
The ticket is now in Pending-Information mode, until 27/05/2005 12:59:58 PM ATL.

16/05/2005 10:26:22 AM Helpdesk
An email notification for a Service Request update has been sent to NC-SYS-DIST-NCCPM-GPNCA-R3

18/05/2005 3:23:26 PM Support
The BF Date has been changed to 08/06/2005 12:59:58 PM ATL.

08/06/2005 1:01:50 PM AR_ESCALATOR
BF Date has expired on ticket # 1292738, an alert has been sent to the Service Desk Duty Officer.

08/06/2005 1:02:48 PM Helpdesk
A BF Date Expiry notification email has been sent to NC-SYS-DIST-NCCPM-GPNCA-R3

08/06/2005 1:25:50 PM Support
The ticket is now in Pending-Support mode, until 30/06/2005 1:21:54 PM ATL.

22/06/2005 11:14:40 AM Support
A Request for reassignment has been sent to the National Service Desk.

Escalation Delay Timer has been re-started.

22/06/2005 11:30:47 AM Helpdesk
The problem is now assigned .

The problem has been re-assigned/escalated to SPRINT CANADA.

22/06/2005 11:30:55 AM Helpdesk
An email notification to Resolver Group for the assignment of the Service Request has been sent to NC-SYS-DIST-SPRINT-R2.

22/06/2005 11:30:59 AM Helpdesk
The problem is now work in progress .

22/06/2005 11:35:32 AM Support
The ticket is now in Pending-Support mode, until 28/07/2005 11:31:14 AM ATL.

Escalation Delay Timer has been stopped.

28/06/2005 3:17:04 PM Support
A Request for reassignment has been sent to the National Service Desk.

Escalation Delay Timer has been re-started.

28/06/2005 3:18:54 PM Helpdesk
The problem is now assigned .

The problem has been re-assigned/escalated to NCCPM-GPNCA.R3.

28/06/2005 3:18:58 PM Helpdesk
An email notification to Resolver Group for the assignment of the Service Request has been sent to NC-SYS-DIST-NCCPM-GPNCA-R3

28/06/2005 3:23:41 PM Support
The ticket is now in Pending-Support mode, until 27/07/2005 3:19:16 PM ATL.

Escalation Delay Timer has been stopped.

07/07/2005 4:09:52 PM Support
The BF Date has been changed to 28/10/2005 3:19:16 PM ATL.

14/10/2005 10:41:08 AM Support
The BF Date has been changed to 25/11/2005 3:19:16 PM ATL.

25/11/2005 3:20:36 PM AR_ESCALATOR
BF Date has expired on ticket # 1292738, an alert has been sent to the Service Desk Duty Officer.

25/11/2005 3:30:25 PM Helpdesk
A BF Date Expiry notification email has been sent to NC-SYS-DIST-NCCPM-GPNCA-R3

25/11/2005 3:35:29 PM Support
The ticket is now in Pending-Release mode, until 31/03/2006 3:34:46 PM ATL.

29/12/2005 2:44:17 PM Support
A Request for incident reassociation has been sent to the National Service Desk.

Escalation Delay Timer has been re-started.

06/02/2006 11:12:21 AM Support
Support assigned this problem to Support

06/02/2006 1:29:28 PM Support
Support assigned this problem to Support.

21/02/2006 1:31:39 PM Support
Support assigned this problem to Support

23/02/2006 2:16:24 PM Support
Support assigned this problem to Support.

28/02/2006 12:30:57 PM Support
Support assigned this problem to Support.

01/03/2006 6:54:25 PM Support
Support assigned this Incident to himself/herself.

29/03/2006 6:38:58 PM Support
The System / Application selection in the Cause Code menu group has be changed to : SYMPOSIUM CALL CENTRE SERVER (SCCS)

The problem is now resolved .

29/03/2006 8:25:25 PM Helpdesk
An email notification has been sent to the client for the closure of his/her Service Request.


29/03/2006 8:25:25 PM Helpdesk
Ticket has been closed by Helpdesk


Uggh,
Phil

 

[lbass]

Try this:

if instr({table.clob}, "in Pending") > 0 then cdatetime(left({table.clob}, 22))

-LB

 

[ITCPhil]

It only prints the first 22 caracters of the CLOB field without going through it (09/05/2005 10:02:32 AM).

 

[lbass]

I didn't see your last post. Are you saying that the above is all ONE record? If so, do you think the breaks make use of chr(13)? Or chr(10)? As a first step, try a test to see what the following returns:

split({table.clob},chr(13))[1]

-LB

 

[ITCPhil]

The command 'split({table.clob},chr(13))[1]' returns the entire field. Using 'split({table.clob},chr(10))[1]' returns the first line of the field

My post above is one record, yes. It's actually twice as long as everything is in French as well but I removed it to save space.

Phil

 

[lbass]

What specifically would you want returned from the above sample field? I know you want the time pending status started, but then don't you also need the time when it is removed from this status? Would it always be the next stated time? Is there language that consistently identifies the second date? I'm assuming you want the difference between those times in seconds.

-LB

 

[ITCPhil]

I want to know how long it was in pending status, but there isn't always a time when it is removed and it's not always the next stated time...

This one one for example was never removed from pending :

08/06/2005 1:25:50 PM Support
The ticket is now in Pending-Support mode, until 30/06/2005 1:21:54 PM ATL.

22/06/2005 11:14:40 AM Support
A Request for reassignment has been sent to the National Service Desk.

And this one the next time is not the time you want :

28/06/2005 3:23:41 PM Support
The ticket is now in Pending-Support mode, until 27/07/2005 3:19:16 PM ATL.

Escalation Delay Timer has been stopped.

07/07/2005 4:09:52 PM Support
The BF Date has been changed to 28/10/2005 3:19:16 PM ATL.

14/10/2005 10:41:08 AM Support
The BF Date has been changed to 25/11/2005 3:19:16 PM ATL.


You are correct in what I want, I need a difference of time but there are many different conditions to identify my status'.

 

[lbass]

It sounds like you don't really want to do this. If you want to move forward, you will have to supply the rules for what constitutes the end of pending status that allows for the variation in conditions that you mention, remembering that I am not familiar with your internal processes.

-LB

 

[ITCPhil]

It's not that I don't want to do it, it's just overwhelming trying to figure out the possibilities and hard to explain. I looked through a few incidents and there are 5 cases, but I might have forgotten some if they are obscure and rarely used.

An incident in pending can be removed out of pending by the following (this always while status is showing pending) :
case0-BF time expires (the default way to do so)
case1-Resolver closes incidents (the BF is often put farther out so they won't get bothered for updates)
case2-Resolver reassigned incident (They get outside assistance and request it be sent there)
case3-Service Desk closes incident ( At Client or supports request)
case4-Service Desk reassigns incident (Internal/external support calls and requests it be sent elsewhere)

Only in the first case is there noticeable text to say an incident was removed out of pending. In the other 4 cases, the lines that are used are the same entries that are used in all tickets regarless of status which are :
case1-The problem is now resolved. (Resolver closes a ticket)
case2-A Request for reassignment has been sent to the National Service Desk. (Resolver wants it reassigned)
case3-The problem is now resolved. (Help Desk closes incident manually)
case4-The problem has been assigned to <Groupname>. (Help Desk reassigns the incident somewhere else, this can either be preceeded by the resolver's request or done with nothing in the memo field)

In addition to this, an incident's pending time can be changed which gives :
-The BF Date has been changed to 28/10/2005 3:19:16 PM ATL.

If I am not missing anything, this ends up with a main 'if' clause for where the pending start and a 'then' for where it ends. The 'else' associated to the first 'if' would contain a secondary 'if' clause for a bf date change and another 'if' clause to look at what is next to see if it contains one of the 4 other cases where it is actioned before going out of pending. It then looks through each case and if is it true it would refer to that as the BF end date instead of looking for an actual BF expiry time.

Hmmm, I might have switched something in there, the BF date change confuses me but it should be the easiest one as it just happens when a ticket is already in pending.

Ouch, I tried to make it clearer, but it doesn't look any easier to understand, sorry about that

Regards,
Phil