Case Statement with the Between Date function

[Mitchell_Collen via AccessMonster.com]

Hi,
I am having trouble creating the logic for a date case statement (this is for
Access with SQL) but I figured you all could help me get this. Its merely
logic, but I just can get it right today. I am making a case statement to
output shift names for each shift at my job. (the shifts are typical) They
are:

shift A = 7:00 to 14:00

shift A and C = 14:01 to 15:30

shift C = 15:31 to 20:30

shift C and Z = 20:31 to 22:30

shift Z = 22:31 to 07:00


I have pasted the snippet of what I have so far below: The query works only
the minutes are off so the shifts fall into the wrong category in the last
few minutes. I just can't get it. I will be writing operation reports based
on aggregated data grouped by these. Please advise.- Misty

SELECT TOP 100 PERCENT 'Shifts' = CASE WHEN Datepart(hh, O.OrderStateTime)
BETWEEN '07' AND '14' AND DATEPART(mi,
O.OrderStateTime) BETWEEN '0' AND '59' THEN 'A' WHEN
(DATEPART(hh, OrderStateTime) = '20') AND (DATEPART(mm, OrderStateTime)
BETWEEN '30' AND '59') OR
(DATEPART(hh, OrderStateTime) = '21') AND (DATEPART(mm, OrderStateTime)
BETWEEN '0' AND '59') OR
(DATEPART(hh, OrderStateTime) = '22') AND (DATEPART(mm, OrderStateTime)
BETWEEN '0' AND '30') THEN 'C and Z'
WHEN (DATEPART(hh, OrderStateTime) = '14') AND (DATEPART(mm, OrderStateTime)
BETWEEN '0' AND '59') OR
(DATEPART(hh, OrderStateTime) = '15') AND (DATEPART(mm, OrderStateTime)
BETWEEN '0' AND '30') THEN 'A and C'
WHEN (DATEPART(hh, OrderStateTime) = '15') AND (DATEPART(mm, OrderStateTime)
BETWEEN '31' AND '59') OR
Datepart(hh, O.OrderStateTime) BETWEEN '16' AND '19' AND DATEPART(mi,
O.OrderStateTime) BETWEEN '0' AND '59' OR (DATEPART(hh,
OrderStateTime) = '20') AND (DATEPART(mm, OrderStateTime) BETWEEN '0' AND
'30') THEN 'C' WHEN (DATEPART(hh, OrderStateTime) = '22') AND (DATEPART(mm,
OrderStateTime) BETWEEN '31' AND '59') THEN 'Z'
WHEN Datepart(hh, O.OrderStateTime) BETWEEN '23' AND '24' OR
Datepart(hh, O.OrderStateTime) BETWEEN '0' AND '6' AND
DATEPART(mi, O.OrderStateTime) BETWEEN '0' AND
'59' THEN 'Z' END

 

[Michel Walsh]

a table and an inner join?


With the tableOfIntervals:

FromThis ToThis WhateverResultYouNeed
00:00:00 07:00:00 x1
07:00:00 14:01:00 x2
14:01:00 15:30:00 x3
....
22:31:00 23:59:59 xN



and then, use something like:

SELECT ..., z.WhateverResultYouNeed

FROM tableName AS x INNER JOIN tableOfIntervals As z
ON ( x.timeField >= z.FromThis
AND x.timeField < z.ToThis )



Hoping it may help,
Vanderghast, Access MVP

 

[Mitchell_Collen via AccessMonster.com]

Michael,
It looks good. But, I forgot to mention (b/c I was not thinking it was
important) that I don't have access to modify or create any new tables.
Thanks for the alternative solution. I didn't think to create any joins.
Thanks, Misty

 

[Michel Walsh]

If you are using Access, while not having the possibility to create table in
the back end, maybe you can create a table in the front end?

Data being in a table, rather than in the code, it can be easily modified,
as company's rules change... Then end user does not have to modify ... your
code... but the data in a table.


Vanderghast, Access MVP