Day and night patrol hours by day

[Matthew P]

Hello!

First off, this is a repost of an earlier request with modifications.

I have data set up in the following format:

Area | Start | Stop

Area is one of five areas in a River.
Start is the start time/date of the patrol.
Stop is the stop time/date of the patrol.

From this data I would like to create a query which shows how many hours
were patrolled during the day and during the night for each day in each area.
The daytime hours would run from 06:00 to 18:00 each day with night hours
taking up the other 12 hours.

Thanks for any help!

Matthew

 

[Michel Walsh]

Hi,


It would be a complex expression, mainly because we have to extract the time
part, compare it, then add it to the date part.


To get the day-time,

CorrectedStart: DateValue(start) + iif( TimeValue(start) > #06:00:00#,
TimeValue(start), #06:00:00#)

CorrectedEnd: DateValue(start) + iif( TimeValue(stop) < #18:00:00#,
TimeValue(stop), #18:00:00#)

TentativeTime: CorrectedEnd - CorrectedStart

DayTime: iif( TentativeTime > 0, TentativeTime, 0)


To get the pre-morning time (from 00:00:00 to 06:00:00) change the limits.
To get the evening time, use 18:00:00 and 23:59:59 limits. To get the night
time, add the pre-morning time to the evening time.


Example: (for day time)

Start= x @ 2:00:00; end = x @ 4:00:00 ==> CorrectedStart= x @ 6:00:00;
CorrectedEnd = x @ 4:00:00, Tentative = -2h, DayTime = 0-

Start = x@ 19:00:00, end = x+1 @ 3:00:00 ==> CorrectedStart = x @
19:00:00 CorrectedEnd = x@ 18:00:00, tentative = -1h, DayTime = 0

Start = x @ 3:00:00, end = x@ 7:00:00 ==> CorrectedStart = x @ 6:00:00,
CorrectedEnd = x @ 7:00:00, tentative = 1h, DayTime = 1h


Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Need to understand your data a bit more in terms of the entry of Start and
Stop

Do Start/Stop times overlap the day period/night period? That is, could you
have a record that would be
Area: 1
Start: January 1, 2006 08:00 AM
End: January 1, 2006 07:00 PM

Do Start/Stop times cover extended periods of more than 24 hours? Example
Area: 1
Start: January 1, 2006 08:00 AM
End: January 3, 2006 07:00 PM

If not then you may be able to adjust the time for calculation purposes by
subtracting 6 hours and then calculating the day shift durations.

 

[Matthew P]

Hello,

Thanks for the quick response to this question.

This looks like a much easier solution than what I was expecting to have to
do, thank you for that. Just about to start testing to see if it all works
out.

Matthew

 

[Matthew P]

Hopefully this clarifies things a bit more:

Start / Stop times can overlap between day and night periods but should not
be covering greater than 24 hours.

The other point I should have noted is that Start/Stops can cross over the
midnight time period. For example:

Start: September 10, 2006 10:30 PM
Stop: September 11, 2006 02:00 AM

In this case I would like the 1.5 hours on the 10th to be included with the
nighttime patrol hours for the 10th and then 2 hours on the 11th to be
included with the nighttime patrol hours there.

Currently I am not sure that this can easily be queried out. Please correct
me if I'm wrong (I'd much rather do it with one query) but I am thinking I
might have to query out records crossing midnight and split them into two
patrols each (as in the example below). After this process I could then run
the day/night queries. Does this sound right or is there something I'm
missing?

Example using above patrol times:
1: Start: September 10, 2006 10:30 PM
Stop: September 11, 2006 00:00 AM
2: Start: September 11, 2006 00:00 AM
Stop: September 11, 2006 02:00 AM

Thanks again.

 

[Matthew P]

Thought I would add an update now that I've given it a try.

The statements worked very well, though I did add a couple of modifications
myself in order to accomodate the patrols that cross over midnight. I'll just
list them briefly below, let me know if you see any obvious flaws or have any
comments - definitely still just learning here.

For the Morning query I have modified the CorrectedStart to run as follows:

CorrectedStart:

iif(DateValue(Exited) > DateValue(Entered), DateValue(Exited),
DateValue(Entered))
+
iif(DateValue(Entered)<DateValue(Exited), #00:00:00#,iif(TimeValue(Entered)

#00:00:00#, TimeValue(Entered), #00:00:00#))

And the Evening as follows:

CorrectedEnd:

DateValue(Entered)
+
iif(DateValue(Exited) > DateValue(Entered), #23:59:59#,
iif(TimeValue(Exited)<#12/30/1899 23:59:59#,TimeValue(Exited),#12/30/1899
23:59:59#))

Thank you very much for the help you gave!

Matthew