Delay counting

[Guest]

Hello
I got date's and hours of arrival and departures trucks to store, but store is open only in workdays (its not equal Mo-Fr!) 6-22. I need to count hours when trucks not work because store us shut.

TruckNo, Arrival, Departure (date format yyyy-mm-dd hh:mm hh 24 hour format )

1, 2004-01-12 21:00 2004-01-13 06:00
2, 2004-01-12 18:00 2004-01-13 01:00
3, 2004-01-13 23:00 2004-01-14 05:00
4, 2004-01-11 21:00 2004-01-15 09:00
2, 2004-01-13 15:00 2004-01-14 02:00
1, 2004-01-13 11:00 2004-01-13 15:00

Day,Open, Closed
2004-01-11, 6 ,22
2004-01-12, 6 ,22
2004-01-13, 6 ,22
2004-01-14, 6 ,22
2004-01-15, 0 ,0
2004-01-16, 6 ,22
2004-01-17, 6 ,22

result

1, 2004-01-12 21:00 2004-01-13 06:00 =8h
2, 2004-01-12 18:00 2004-01-13 01:00 =3h
3, 2004-01-13 23:00 2004-01-14 05:00 =6h
4, 2004-01-11 21:00 2004-01-15 09:00 =8+8+8+11=35h
2, 2004-01-13 15:00 2004-01-14 02:00 =4h
1, 2004-01-13 11:00 2004-01-13 15:00 =0h
or better for my use

1, 2004-01-12 22:00 2004-01-13 06:00 =6h
2, 2004-01-12 22:00 2004-01-13 01:00 =3h
3, 2004-01-13 23:00 2004-01-14 05:00 =6h
4, 2004-01-11 22:00 2004-01-12 06:00 +
4, 2004-01-12 22:00 2004-01-13 06:00 +
4, 2004-01-13 22:00 2004-01-14 06:00 +
4, 2004-01-14 22:00 2004-01-15 09:00 = 8+8+8+11=35h

2, 2004-01-13 22:00 2004-01-14 02:00 =4h

1, 2004-01-13 11:00 2004-01-13 11:00 =0h
or
1, 2004-01-13 15:00 2004-01-13 15:00 =0h

Thanks

 

[Michel Walsh]

Hi,



Assuming Open and Close are with DATE and time values, a record from
the shop schedule is not related to a record in truck if (and only if):


Open > Departure OR Close < Arrival


There are some "overlap" ( partial or full) in the negation of the
preceding (De Morgan) :


Open <=Departure AND Close >=Arrival


So, for a given record in Trucks, we can keep just the relevant
records in ShopSchedule with the following join:



================================
SELECT TruckNo,
24.0* SUM( Departure-Arrival),
24.0* SUM( iif( Nz(Close, Departure) > Departure, Departure,
Nz(Close, Departure)) -
iif(Arrival > Nz(Open, Arrival), Arrival, Nz(Open,
Arrival)) )

FROM Trucks AS t LEFT JOIN ShopSchedule As s
ON (s.Open <= t.Departure)
AND ( s.Close >=t.Arrival )

GROUP BY TruckNo
====================================


To compute the total time the truck is present WHILE the shop is
open:

If the truck is always present while the shop is open (full overlap)
for a given record, Close < Departure, and Closing time should be used as
ending time (since we want the time where the truck is present AND the shop
open). Same, Arrival < Open, and we must use Open as starting time. The
others case are resolved the same way.

We multiplied by 24 to get a result in hours.



Hoping it may help,
Vanderghast, Access MVP

Hello
I got date's and hours of arrival and departures trucks to store, but

store is open only in workdays (its not equal Mo-Fr!) 6-22. I need to count
hours when trucks not work because store us shut.