Calculating productive time for resources with multiple time stamps 3

[jkupov]

I'm needing to calculate the amount of time that our company trucks are being actively used based off of the driver's actual login times from the dispatch software on their tablets. The problem is the database seems to have multiple records for each time status. I'm not sure why but in the meantime I still need to have an idea of our resource usage. Here is a sample of the data for one driver. I've added column letters and row numbers to help.

A B C D
Date Truck Status Time
1 7/6/2015 D1549 Available 6:58:13
2 7/6/2015 D1549 Break 9:02:21
3 7/6/2015 D1549 Break 9:02:21
4 7/6/2015 D1549 Unavailable 9:17:46
5 7/6/2015 D1549 Unavailable 9:17:46
6 7/6/2015 D1549 Available 9:37:54
7 7/6/2015 D1549 Available 9:37:54
8 7/6/2015 D1549 Unavailable 10:03:06
9 7/6/2015 D1549 Unavailable 10:03:06
10 7/6/2015 D1549 Available 10:11:38
11 7/6/2015 D1549 Available 10:11:38
12 7/6/2015 D1549 Lunch 11:04:16
13 7/6/2015 D1549 Lunch 11:04:16
14 7/6/2015 D1549 Available 12:05:58
15 7/6/2015 D1549 Available 12:05:58
16 7/6/2015 D1549 Break 14:04:12
17 7/6/2015 D1549 Break 14:04:12
18 7/6/2015 D1549 Log Off 16:03:39
19 7/6/2015 D1549 Log Off 16:03:39

So I have two questions. The first is what do I do with the duplicate entries (e.g. rows 2&3, 4&5, etc)? The second, and more important question is how to tally up the actual time in the 'Available' status for each truck? This is a sample from just one truck but we have hundreds and I would like to just sum up the times available. So in this example, I don't necessarily care that the driver took a break at 9:02:21 followed by an undefined 'unavailable' at 9:17:46. I just care about the blocks of time when the truck was being used productively. Jeez. I hope that makes sense. If not ask and I can clarify.

 

[CoSpringsGuy]

By the way
Global Variable share data throughout report only. Which is the default and why you don't see that in my formulas above.
Shared variables also share data throughout the report as Charliy mentioned but are different because they allow you to share with subreports as well
Local variables are only available within the formula

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[jkupov]

COSpringsGuy Thank you! That actually makes a lot of sense. I tried it out and so far so good!

 

[n2nuk]

Hi jkupov and CoSpringsGuy

I have a similar report that i am having difficulty with and am hoping you may be able to offer some assistance, unfortunately i cant get the the comprehensive solution offered by CoSpringsGuy to work for me

I am trying to work out the number of hours between certain types of contacts that are undertaken with a client.

my dataset looks like this:

clientID[tt] [/tt]Contact Code[tt] [/tt]Contact Date
12345[tt] [/tt]Initial Contact[tt] [/tt]12/01/2014 10:30
12345[tt] [/tt]Telephone call[tt] [/tt]12/01/2014 12:25
12345[tt] [/tt]Telephone call[tt] [/tt]12/01/2014 13:47
12345[tt] [/tt]Information Requested[tt] [/tt]12/01/2014 14:54
12345[tt] [/tt]Information Recieved[tt] [/tt]13/01/2014 08:30
12345[tt] [/tt]Telephone call[tt] [/tt]13/01/2014 11:10
12345[tt] [/tt]End Contact[tt] [/tt]13/01/2014 13:45
12345[tt] [/tt]Initial Contact[tt] [/tt]17/03/2014 23:47
12345[tt] [/tt]Telephone call[tt] [/tt]18/03/2014 08:07
12345[tt] [/tt]Telephone call[tt] [/tt]18/03/2014 11:23
12345[tt] [/tt]Information Requested[tt] [/tt]18/01/2014 17:11
12345[tt] [/tt]Information Recieved[tt] [/tt]19/01/2014 10:43
12345[tt] [/tt]End Contact[tt] [/tt]19/01/2014 15:53

I need to work out the hours between each initial and end contact and highlight the number of hours between initial and end contacts.

I have filtered the report so that i am returning the initial contact and end contact. I need some help with working out how to calculate the hours between the contacts and how to separate out or group each episode that had a initial and return

Many thanks

 

[CoSpringsGuy]

You should probably start a new question to get more responses but here is an attempt at your question. I assume you are grouped by clientID?
Place the following formula in group header and suppress

datetimevar InitialT := datetime(0,0,0,0,0,0);
datetimevar EndT := datetime(0,0,0,0,0,0);
numbervar TotalT := 0;
numbervar CTCTime;
whileprintingrecords

Place this in details

datetimevar InitialT;
datetimevar EndT;
numbervar TotalT;
numbervar CTCTime;
whileprintingrecords;
If {YourTable.Contact Code} = "Initial Contact" then (
    CTCTime := 0;
    InitialT :={YourTable.DAteTimeField};
    datetime(0,0,0,0,0,0)    )
Else 
if {YourTable.Contact Code} = "End Contact" then (
    EndT := {YourTable.DAteTimeField};
    CTCTime := Datediff("s",InitialT,EndT);
    TotalT := TotalT + CTCTime;
    EndT)
Else 
(CTCTime := 0;
datetime(0,0,0,0,0,0)
);
CTCTime

Place this code in Group Footer

numbervar TotalT;
whileprintingrecords;
TotalT

To display seconds in hh:mm:ss refer to the solution above regarding display method.



_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection