How to split/extrapolate records by date range

[kiwipeet]

Hi

I am trying to write a query to report vehicle utilisation of fleet cars.

I have records for each booking with a start and end date. The booking could start and finish on the same day, or could span many days.

I want to expand each record that I have one entry for each day of the booking. For example:

11/01/2010 15:30 12/01/2010 16:00

would become

11/01/2010 15:30 12/01/2010 23:59
12/01/2010 00:01 12/01/2010 16:00

Can this be done with a query? or is this a VBA question?

Any help would be greatly appreciated.

Thanks in advance.

Pete

 

[MarkK]

I would recommend that you not split up your dates like that. Time is just a quantity like dollars or pounds or gallons or something. It doesn't make sense to chop it all up like that.

 

[kiwipeet]

I would recommend that you not split up your dates like that. Time is just a quantity like dollars or pounds or gallons or something. It doesn't make sense to chop it all up like that.

Thanks for your reply.

Normally I would agree but in this case I see it as the most practical way of addressing other issues I have.

I need to to provide analysis of records in various time bands, i.e during office hours, vs peak 8am-5pm or 10am-3pm or 10-12. and also take into account whether they are weekend or weekdays.

By splitting into mutiple records I can still sum the total duration for a booking, total utilisation for all cars for a given day, during and/or outside work hours.

Imagine 1 car booked in and out 3 times on a thursday, then taken 4:00pm thursday afternoon and returned monday mid-day, Then booked out again Monday afternoon.

I need to report total time out (thurs-mon), total utilisation during office hours, (8am-5pm thurs, fri, mon), utilisation during peak hours, (10am-3pm thurs, fri, mon), and total time out side of work hours.

 

[MarkK]

The structure I recommend is that you treat the departure of a car and the arrival of a car as separate things that differ only by direction so that each arrival and each departure are a single record in the same table. That table, for example, might look like ...

tCarEvent
CarEventID (PK)
CarID (FK)
DateTime
Direction

The direction field is either a minus one (-1) or a one (1), and I'd use -1 when a car departs.
This allows you to do very sophisticated date math very simply because you can multiply the date/time value by the direction and do simple sums.
One thing you can do is query whether the car is in the lot now using SQL like ...

SELECT SUM(Direction) As CarIsHere 
FROM tCarEvent
WHERE CarID = 12
  AND DateTime < Now()

If the result of that query is 0 (or -1, depending how you handle when the car first arrives) the car is not here now and that works for any moment in time.
And again, the time a car is out is the sum of (DateTime * Direction) which subtracts the departure time from the arrival time for each cycle.
So there's enough info to determine whether a car is present at any point in time, and how long it was gone between any two times, and I bet everything you need is a subset of that is some form.
Anyway, that's how I'd approach the problem and maybe it gives you another way to think about it too,
Cheers,