Case Statement with the Between Date function

[Mitchell_Collen via AccessMonster.com]

Hi, (I posted this in the query area earlier) I don't know how to link post
yet.
Anyway, 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

 

[Sylvain Lafontaine]

Hum, difficult question that should be best answered in a newsgroup
dedicated specifically to SQL-Server programming such a
m.p.sqlserver.programming. However, here a two simpler solutions:

declare @dt as datetime
set @dt = '15:30:00.002'

select @dt as dt, q.t as ShifTime,
case
when q.t < '07:00' or q.t > '22:30:00:000' then 'Z'
when q.t <= '14:00:00:000' then 'A'
when q.t <= '15:30:00:000' then 'B'
when q.t <= '20:30:00:000' then 'C'
else 'C and Z' End as ShiftName
from (
select convert (char(12), @dt, 114) as t
) as q


select @dt as dt, q.t as ShifTime,
case
when q.t between '07:00' and '14:00:00:000' then 'A'
when q.t between '14:00:00:001' and '15:30:00:000' then 'B'
when q.t between '15:30:00:001' and '20:30:00:000' then 'C'
when q.t between '20:30:00:001' and '22:30:00:000' then 'C and Z'
else 'Z' End as ShiftName
from (
select convert (char(12), @dt, 114) as t
) as q


Three things; first, in the above examples, I've put testing stuff around
the case statement by using a subquery. Of course, you don't need this part
in your own select statements.

Second, on sql-server the precision is down to 3.33 millisecond, so
'15:30:00.002' is translated to '15:30:00.003' while '15:30:00.001' will be
translated to '15:30:00.000'.

And third, the output format for 114 is hh:mm:ss:mmm, with : as the
separation for the milliseconds. Other formats could use a point as in
hh:mm:ss.mmm. Instead of the format 114, you could use the format 108 or
truncate to 8 characters instead of 12 but then, a value such as
15:30.00.003 will get truncated to 15:30:00 and not to 15:30:01.

 

[Mitchell_Collen via AccessMonster.com]

Sylvain.
I ran this and tested it and it showed the correct shifts so I am excited to
apply this. However, I don't understand how to make it work with my table and
column name.

I have put Select OrderStateTime From OrdState right after the declare @dt
statement. I tried to replace the time (set @dt = '15:30:00.002') and then
set @dt = OrderStateTime. This didn't work. I was able to put the select
statement in the stored procedure but it only showed the OrderStateTime
column and it didn't run through the cases. Or at least, maybe it did but I
didn't see it. Do you know where I can put my column name so that it will
show the shift for each value? Thanks so much for helping me.

ALTER PROCEDURE StoredProcedure5
AS
DECLARE @dt as DateTime
SELECT OrderStateTime
FROM dbo.phmOrdState
SELECT @dt AS dt, q. t AS ShifTime, CASE WHEN q. t < '07:00' OR
q. t > '22:30:00:000' THEN
'Z' WHEN q.............

 

[Sylvain Lafontaine]

The problem that you have here is about the reuse of the result of an
expression to other expressions in the same select statement. You can't do
this directly with SQL-Server but it's easy to do it indirectly. There are
many possibilities:

1- Repeat the expression (« convert (char(12), @dt, 114) ») each time.

2- Use a User Defined Function (UDF).

3- Use a temporary table

4- Use a computed View.

5- Use a subquery.

In your case, when trying to create a subquery, you have forgot to put a set
of parenthesis around the subquery; these parenthesis are mandatory. Look
carefully at the exemple that I gave you previously.