Querying for Third Shift

dmeiser · *nix forums beginner Joined: 20 May 2006 Posts: 7

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.

s.sathyaram@googlemail.co · *nix forums beginner Joined: 20 Feb 2006 Posts: 13

How about

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

Sathyaram

dmeiser · *nix forums beginner Joined: 20 May 2006 Posts: 7

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:

Chris · *nix forums beginner Joined: 10 Nov 2005 Posts: 23

dmeiser wrote:

dmeiser · *nix forums beginner Joined: 20 May 2006 Posts: 7

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

Google