Querying for Third Shift

[dmeiser]

Hello all:

I need to query a table for events that happen on third shift ( 11 PM
to 7 AM ) in a date range. In the past, I've only needed to query for
one night, but I now need to query for 14 days. Normally, I would say
WHEN Col BETWEEN TimeStamp1 And TimeStamp2, but obviously that won't
work in this case.

For first shift I was able to do WHEN DateCol BETWEEN Date1 And Date2
AND WHEN TimeCol BETWEEN Time1 AND Time2, but for third shift this
obviously won't work.

I could use two different queries and, for third shift, say WHEN ...
AND TimeCol BETWEEN Time1 AND 11:59:59 and then UNION it with WHEN
TimeCol BETWEEN 00:00:00 AND Time2. However, since this will be linked
into an MS Access report, I'd prefer not to have to maintain two
different reports and copy the report back and forth if I make a change
to the SQL or report layout.

Is there any way to do this without either a UNION query or an SQL
function?

Thanks for any help.

[Sathyaram Sannasi]

How about

col between timestamp1 and timestamp2 and (time(col) 2300 or
time(col) < 0700)

Sathyaram

[dmeiser]

I thought this might work, but then I realized I had left something
out: I'm selecting a maximum and minimum time for an event for each
business day in the two week period.

Since our business day starts the previous day at about 11:00 PM
(really, whenever third shifters start), the minimum time for that
business day might be 11:15 PM the previous day, while the maximum time
might be 6:30 AM on the actual day. The whole reason behind getting
the maximum and minimum times is to figure out the time difference
between them.

Perhaps I do need two queries: one with performing a union between
times 11:00 PM Times < 7:00 AM and the second for day shifters?

Sathyaram Sannasi wrote:

How about

col between timestamp1 and timestamp2 and (time(col) 2300 or
time(col) < 0700)

Sathyaram

[ChrisC]

dmeiser wrote:

>...
Since our business day starts the previous day at about 11:00 PM
(really, whenever third shifters start), the minimum time for that
business day might be 11:15 PM the previous day, while the maximum time
might be 6:30 AM on the actual day. The whole reason behind getting
the maximum and minimum times is to figure out the time difference
between them.

If you have a timestamp column containing this information (as opposed
or in addition to a broken out date and time columns), then Sathyaram's
solution will work as he wrote it - the first part of the query can
take care of the overlapping dates. If the dates are broken out, then
the union might work best.

If you have timestamps, you can rewrite Sathyaram's query to do this by
using between, too. Use:

col between timestamp1 and timestamp2 and (time(col + 8 hours) between
0700 and
and 1500)

-Chris

[dmeiser]

I realized how to do this after some time by doing the following:

Select column1, sum(column2), Max(Time), Min(Time), From (
Select column1, column2, time from file where time <= midnight
Union Select column1, column2, time from file where time >= midnight
and time <= 7:00 AM
) Group By column1 Order by column1