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.

 

[SkipVought]

Hi

Your query ought to return unique rows.

 

[jkupov]

Right, as I said, we're working on that part of it. It appears to be a glitch in the app on the tablets which is double time stamping the status updates. In the meantime I still need to figure this out.

 

[Charliy]

The duplicates shouldn't be a problem since the times are the same. The difference in time between the "good" record and the duplicate will always be 0.

 

[SkipVought]

This would be a snap in Excel.

[pre]
Date Truck Status Time ActStat From To Dur
7/6/15 D1549 Available 6:58:13 1 6:58:13 9:02:21 2:04:08
7/6/15 D1549 Break 9:02:21 0 0:00:00 0:00:00 0:00:00
7/6/15 D1549 Unavailable 9:17:46 0 0:00:00 0:00:00 0:00:00
7/6/15 D1549 Available 9:37:54 1 9:37:54 10:03:06 0:25:12
7/6/15 D1549 Unavailable 10:03:06 0 0:00:00 0:00:00 0:00:00
7/6/15 D1549 Available 10:11:38 1 10:11:38 11:04:16 0:52:38
7/6/15 D1549 Lunch 11:04:16 0 0:00:00 0:00:00 0:00:00
7/6/15 D1549 Available 12:05:58 1 12:05:58 14:04:12 1:58:14
7/6/15 D1549 Break 14:04:12 0 0:00:00 0:00:00 0:00:00
7/6/15 D1549 Log Off 16:03:39 0 0:00:00 :00:00 0:00:00
[/pre]

 

[jkupov]

Thanks SkipVought,

That is similar to what I'm doing now but to get this by truck for 678 trucks is quite a chore in excel since I need to also subtotal the total hours for each truck. I am hoping to be able to use Crystal to do most of the work.

 

[SkipVought]

no problem in Excel for multiple trucks: just do a PivotTable. Whole thing could be done in less than 10-15 minutes!

 

[jkupov]

Okay Thanks SkipVought, So what I hear you saying is that this cannot be done in Crystal Reports? Or that you think it will be easier to simply export data only and run a Pivot in Excel? If there is a way to do this in CR it would be helpful to learn it so I can gain those skills when needed in the future.

 

[SkipVought]

Well maybe a CR guru can explain how what you need can be accomplished easily.

 

[jkupov]

Any CR Gurus out there who can explain how to do this?

 

[SkipVought]

So what database is your CR accessing to get the data?

What type of database is it?

 

[jkupov]

Hi SkipVought, we are on Oracle 11g

 

[SkipVought]

If You have access to Oracle, it can be queried directly in Excel to return the resultset you want. This QueryTable remains active in the sheet so that the query can be refreshed on command to get new data. The Excel formulas can be entered one time to get the Available duration for each Date/Truck, which is what I believe you need. The Pivot uses the QueryTable results as source. Once set up, should take a few seconds to run to sum available times per date per truck.

Post a thread in forum68 to get any Excel related help.

 

[jkupov]

Thank you SkipVought! I do have access via ODBC to the Oracle DB. I like this solution a lot.At the same time, I would love to hear how someone would go about doing this in CR. I think your solution is great for an immediate result. At the same time I want to be able to learn new CR skills whenever possible so if anyone knows how this would be accomplished in CR that would be great too.

I'm sure there's a way!

 

[SkipVought]

There's no reason why you could not pursue an immediate solution while you wait for a CR solution.

 

[jkupov]

Agreed

 

[Charliy]

You can do this is Crystal using shared variables. It's just very complicated to explain.

 

[jkupov]

Thanks Charily. I have not used shared variables yet but do remember coming across a section on them in my CR Reference Guide. This is something I'll have to brush up on. The basic idea is to have sub reports pass their results to the main report right?

 

[Charliy]

Shared variables let you share data between formulas.
You would have a formula in you etail section something like this:

shared numbervar trucktime;
shared numbervar totaltime;
if {table.tatus} = "Available" and {table.truck} next({{tal.truck}
then (trucktime :=trucktime+ next({table.time}) - {table.time}; totaltime := totaltime+ next({table.time}) - {table.time})
else (trucktime := trucktime; totaltime := totaltime)

Yu would need other formulas to initalize, reset, and display the times.

 

[CoSpringsGuy]

So not sure why this interested me but...
Issue one as mentioned in original post. The first is what do I do with the duplicate entries You may want to determine why yo are getting these. Are there actually duplicate entries in the table or is this because of the way the table is linked to other tables. If it is one individual table then I am guessing there may be a way to use an SQL statement to select distinct records but I am not familiar enough with Oracle to offer a solution for that. You can however suppress the detail if certain fields are the same as previous records.

{yourtable.Truck} = next({yourtable.Truck})
and
{yourtable.Status} = next({yourtable.Status})
and
{yourtable.Time} = next({yourtable.Time})

This will change your example above from

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


to


1 7/6/2015 D1549 Available 6:58:13
2 7/6/2015 D1549 Break 9:02:21
4 7/6/2015 D1549 Unavailable 9:17:46
6 7/6/2015 D1549 Available 9:37:54
8 7/6/2015 D1549 Unavailable 10:03:06
10 7/6/2015 D1549 Available 10:11:38
12 7/6/2015 D1549 Lunch 11:04:16
14 7/6/2015 D1549 Available 12:05:58
16 7/6/2015 D1549 Break 14:04:12
18 7/6/2015 D1549 Log Off 16:03:39
As Charliy mentioned above this wont effect the computations you are looking for.
Your second issue regarding available time can be solved in CR as follows. First I will assume that IS a time field and not a date time field. if date time there can be some modifications made.

Create this formula and drop in the detail section

next({yourtable.time})

This will give you the To time as in Skips Excel sheet
create another formula and place in details

if {yourtable.Status} = "Available" then {@to_time}-{yourtable.Time1} else  0;

This will give you available time in seconds.
right click the formula - select format fields - common tab then find Display String. Click the X-2 button to the right and enter the following formula

numberVar dur ;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;
if {yourtable..Status} = "Available" then dur := CurrentFieldValue else dur := 0;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);
hhmmss := totext(hrs,"0") + ":" + totext(min,"00") + ":"+totext(sec,"00");
hhmmss

This will display seconds as hh:mm:ss and look like skips spreadsheet.

I would assume your next challenge is going to be to provide total available time for each truck? Thats not quite as easy as you cannot summarize the formula AvailableTime because it uses the next function and cannot be evaluated that way.
So here is a solution for that as well
Create this formula and place in the group header: (you will have to suppress this formula later so that it doesnt appear on your report)

numbervar c := 0;
whileprintingrecords

Create this formula and place in the detail section: (you will have to suppress this formula later so that it doesnt appear on your report)

numbervar c;
whileprintingrecords;
if {yourtable.Status} = "Available" then c := c + next({@to_time})-{yourtable.Time1};
c

Create this formula and place in the group footer. You wont have to suppress this one because this is the one we are after

numbervar c;
whileprintingrecords;
c

Notice this is also in seconds .. You can use the same display method above to display in seconds but will need a slight modification

numberVar dur := CurrentFieldValue ;
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,"0") + ":" + totext(min,"00") + ":"+totext(sec,"00");
hhmmss

If anywhere in here is yells because you have a datetime field and not a time field then we could rewrite all of this or you can just create another formula and use it in place of {yourtable.time} in the above solution

time({yourtable.time})

I tested all of the above with sample data and it works fine but in copying and pasting I may have messed something up so if it doesnt work give me a shout and I will try to help fix it although my time is going to be limited over the next several days.
My output

Good Luck!

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection