Calculate Days Worked and Days Off

[Nimisis]

In Transportation business and use SQL Server with CR 10.
I am working from data from a table that shows:

{allhistory.FromTime} //pickup times
{allhistory.TimeDone} //Time rides completed
{allhistory.DriverName} //driver names
{allhistory.RideID}. //confirmation numbers of trips ran by driver.

I can formulate how many trips a driver has ran between two dates. I can also show what percentage of the total trips a particular driver has ran overall.

I have placed these counts in a group which resets after each driver, so all 70 drivers are displayed in name order.

I would like to calculate how many days each driver has ran trips, keeping in mind they may run several trips a day. This will give me how many days a driver has worked. Then, I would like to calculate how many days a driver has NOT worked. 75% of the drivers are part-time so days off would reflect why the percentage may be lower than other drivers.
We are a 24/7 365 day company so holidays aren't a factor.

So far, my results reflect the same amount of days as there are trips. Do I add a second group for {allhistory.FromTime} and have it reset on change of Day?

 

[lbass]

Do trips ever take more than one day? Are the From and Done fields datetimes?

-LB

 

[Nimisis]

Rarely but yes. Every once in a while, trips will last more than a day (ovevernight trips) where drivers may stay in hotel and return following day. The {allhistory.TimeDone} shows the time the driver is placed off-duty.

 

[Nimisis]

oops, failed to answer your question totally,
the format for both appear like this:
CDateTime (2005, 04, 17, 20, 09, 34)

 

[lbass]

First, you should either create SQL expressions {%fromdate} and {%todate} like this:

trunc(allhistory.`fromtime`)

..to eliminate the time. Or you could wrap each of your datetimes in date(), as in {@fromdate}:

date({allhistory.fromdate})

Then you can insert distinctcounts on the expression or formula within the driver group. You would then need to add a correcting formula to account for trips that cross dates, let's call it {@twodaytrip}:

if {%fromdate} <> {%timedone} then 1

Then in another formula, use the following for the total days worked:

distinctcount({%fromdate},{allhistory.drivername})+
sum({@twodaytrip},{allhistory.drivername})

Not sure how to determine days not worked, unless you come up with a total possible and then subtract the above formula.

-LB

 

[Nimisis]

Enlightenment! Thanks.

I am a novice at best. Could to type the complete part for
trunc(allhistory.`fromtime`)?

I tried truncate (allhistory.`fromtime`) but get an error on (allhistory.

Thanks

 

[lbass]

You have to use punctuation on your field appropriate to your datasource, so look in database->show SQL query and observe the punctuation there, and then use that. Note that "trunc" is the correct usage, NOT "truncate".

You don't HAVE to use a SQL expression, as I noted earlier. It's just more efficient.

-LB

 

[Nimisis]

This has resolved my problems, thanks TONS for the help.