Fueling vehicles with certain time range

[CR4Reid]

Hello,

I need to count distinct vehicle numbers within a 2 week time frame of how much fueling was done. Fueling is done at 8 am and continues through the night until 7:59 am the next morning. This counts as the same day.

So if on Feb 11 8:30 am, car 1234 was fueled, that counts as one. Then on Feb 12 at 4:30 am, car 1234 is fueled again, that counts as 2 for Feb 11th, and not feb 12th. The cutoff time is 8:00 am.

How do I distinct count the number of vehicles in a 2 week period?

Sample data

Car date time qty(L)
1234 02/11 8:30 30
1234 02/11 17:30 10
1234 02/12 4:30 20
2345 02/12 8:50 30
2345 02/12 15:30 10
2345 02/13 5:30 20
3456 02/11 11:30 55
3456 02/11 14:30 10
3456 02/12 9:30 20

Result:

count car# 1234 for 02/11 is 3 fill ups totaling 60L
count car# 1234 for 02/12 is 1 fill up totaling 30L
count car# 2345 for 02/12 is 3 fill ups totaling 60L
count car# 3456 for 02/11 is 2 fill ups totaling 65L
count car# 3456 for 02/12 is 1 fill up totaling 20L

I can't seem to get a formula working. Any help is appreciated. Thanks,

CR

 

[Charliy]

I would group by vehicle number, then have a Running Total that does a count of the Time (or any other field)
then place the Vehicle ID and the Running Total in the Group Footer (suppress everything else. Make sure the Running Total resets on change of goup.

 

[CR4Reid]

Thanks Charliy. I'm going to have to mess with it some more.

 

[PeterDimitrov]

You can create a formula to deduct 8 hours from the fueling date: Date(DateAdd("h",-8, <your date field>))
This formula will return 2/18/2014 for everything in the period 2/18/2014 8:00 AM - 2/19/2014 7:59 AM
Group by vehicle and the new formula and add summaries.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[CR4Reid]

Awesome! Thanks Peter!