shawks
March 11th, 2008, 12:34 PM
I am using Crystal 10. I am trying to find the total amount of time field techs spend at a site for a case. For example if techs visit a site four different times we need to find the duration of each visit to determine the grand total of time at the site. The next case could have one or more visits or even zero visits.
The table I am using lists an event (Arrive, Depart, Close Case, etc - may not be related to the field tech - such as the calls to help center or dispatching to field tech) with a time/date stamp for the event plus site id, case id, tech name, etc. So for the example above there would be four "Arrive" events and four "Depart" events (unless the last interaction was "Close Case" then there would be three "Departs" and the last would be "Close Case").
The records are currently being grouped by site id and then case id.
Below are some sample data from one case. The total labor time for techs at the site is about 21 minutes. It is possible that non-onsite X_EVENTs could occur before, after, or during the field tech's visit. There are none in this case but it could occur.
CASE_ID CREATION_TIME CASE_STATUS CASE_CONDITION X_EVENT X_ADDNL_INFO X_EVENT_TIME SITE_ID ONSITE_TECH
2232467 2/12/2008 10:36:40 AM Closed-FS-OnSite Fix Closed Arrive 2/28/2008 11:44:29 AM 10101 12345
2232467 2/12/2008 10:36:40 AM Closed-FS-OnSite Fix Closed Arrive 2/28/2008 12:05:09 PM 10101 54321
2232467 2/12/2008 10:36:40 AM Closed-FS-OnSite Fix Closed Close FS-OnSite Fix 2/28/2008 12:05:57 PM 10101 54321
2232467 2/12/2008 10:36:40 AM Closed-FS-OnSite Fix Closed Departed Part Ordered-Down 2/28/2008 12:04:46 PM 10101 12345
2232467 2/12/2008 10:36:40 AM Closed-FS-OnSite Fix Closed First Dispatch Field Queue 2/12/2008 10:37:16 AM 10101 22377
Thanks for any assistance you can provide.
The table I am using lists an event (Arrive, Depart, Close Case, etc - may not be related to the field tech - such as the calls to help center or dispatching to field tech) with a time/date stamp for the event plus site id, case id, tech name, etc. So for the example above there would be four "Arrive" events and four "Depart" events (unless the last interaction was "Close Case" then there would be three "Departs" and the last would be "Close Case").
The records are currently being grouped by site id and then case id.
Below are some sample data from one case. The total labor time for techs at the site is about 21 minutes. It is possible that non-onsite X_EVENTs could occur before, after, or during the field tech's visit. There are none in this case but it could occur.
CASE_ID CREATION_TIME CASE_STATUS CASE_CONDITION X_EVENT X_ADDNL_INFO X_EVENT_TIME SITE_ID ONSITE_TECH
2232467 2/12/2008 10:36:40 AM Closed-FS-OnSite Fix Closed Arrive 2/28/2008 11:44:29 AM 10101 12345
2232467 2/12/2008 10:36:40 AM Closed-FS-OnSite Fix Closed Arrive 2/28/2008 12:05:09 PM 10101 54321
2232467 2/12/2008 10:36:40 AM Closed-FS-OnSite Fix Closed Close FS-OnSite Fix 2/28/2008 12:05:57 PM 10101 54321
2232467 2/12/2008 10:36:40 AM Closed-FS-OnSite Fix Closed Departed Part Ordered-Down 2/28/2008 12:04:46 PM 10101 12345
2232467 2/12/2008 10:36:40 AM Closed-FS-OnSite Fix Closed First Dispatch Field Queue 2/12/2008 10:37:16 AM 10101 22377
Thanks for any assistance you can provide.