All Forums Database
amerid 9 posts Joined 02/13
11 Nov 2013
Case statement not pulling time between 18:31:01 and 19:00:00

My data has four columns date, departure, destination and depart time. I have a case statement that groups depart times. It works fine but it does not group departures between 18:31 and 19:00. Time is
Any idea why not? Thank you for your help.
SEL
dept_date,
depart,
dest,
depart_tm ,
CASE
WHEN depart_tm BETWEEN '00:00:01' AND '00:30:00' THEN '0000AM'
WHEN depart_tm BETWEEN '00:31:00' AND '01:00:00' THEN '0030AM'
WHEN depart_tm BETWEEN '01:01:01' AND '01:30:00' THEN '0100AM'
WHEN depart_tm BETWEEN '01:31:00' AND '02:00:00' THEN '0130AM'
WHEN depart_tm BETWEEN '02:01:00'AND '05:00:00' THEN '0200AM'
WHEN depart_tm BETWEEN '05:01:00' AND '05:30:00' THEN '0500AM'
WHEN depart_tm BETWEEN '05:31:00' AND '06:00:00' THEN '0530AM'
WHEN depart_tm BETWEEN '06:01:00' AND '06:30:00' THEN '0600AM'
WHEN depart_tm BETWEEN '06:31:00' AND '07:00:00' THEN '0630AM'
WHEN depart_tm BETWEEN '07:01:00' AND '07:30:00' THEN '0700AM'
WHEN depart_tm BETWEEN '07:31:00' AND '08:00:00' THEN '0730AM'
WHEN depart_tm BETWEEN '08:01:00' AND '08:30:00' THEN '0800AM'
WHEN depart_tm BETWEEN '08:31:00' AND '09:00:00' THEN '0830AM'
WHEN depart_tm BETWEEN '09:01:00' AND '09:30:00' THEN '0900AM'
WHEN depart_tm BETWEEN '09:31:00' AND '10:00:00' THEN '0930AM'
WHEN depart_tm BETWEEN '10:01:00' AND '10:30:00' THEN '1000AM'
WHEN depart_tm BETWEEN '10:31:00' AND '11:00:00' THEN '1030AM'
WHEN depart_tm BETWEEN '11:01:00' AND '11:30:00' THEN '1100AM'
WHEN depart_tm BETWEEN '11:31:00' AND '12:00:00' THEN '1130AM'
WHEN depart_tm BETWEEN '12:01:00' AND '12:30:00' THEN '1200N'
WHEN depart_tm BETWEEN '12:31:00' AND '13:00:00' THEN '1230PM'
WHEN depart_tm BETWEEN '13:01:00' AND '13:30:00' THEN '0100PM'
WHEN depart_tm BETWEEN '13:31:00' AND '14:00:00' THEN '0130PM'
WHEN depart_tm BETWEEN '14:01:00' AND '14:30:00' THEN '0200PM'
WHEN depart_tm BETWEEN '14:31:00' AND '15:00:00' THEN '0230PM'
WHEN depart_tm BETWEEN '15:01:00' AND '15:30:00' THEN '0300PM'
WHEN depart_tm BETWEEN '15:31:00' AND '16:00:00' THEN '0330PM'
WHEN depart_tm BETWEEN '16:01:00' AND '16:30:00' THEN '0400PM'
WHEN depart_tm BETWEEN '16:31:00' AND '17:00:00' THEN '0430PM'
WHEN depart_tm BETWEEN '17:01:00' AND '17:30:00' THEN '0500PM'
WHEN depart_tm BETWEEN '17:31:00' AND '18:00:00' THEN '0530PM'
WHEN depart_tm BETWEEN '18:01:00' AND '18:30:00' THEN '0600PM'
WHEN depart_tm BETWEEN '18:31:00' AND '19:00:00' THEN '0630PM'
WHEN depart_tm BETWEEN '19:01:00' AND '19:30:00' THEN '0700PM'
WHEN depart_tm BETWEEN '19:31:00' AND '20:00:00' THEN '0730PM'
WHEN depart_tm BETWEEN '20:01:00'AND '20:30:00' THEN '0800PM'
WHEN depart_tm BETWEEN '20:31:00' AND '21:00:00' THEN '0830PM'
WHEN depart_tm BETWEEN '21:01:00'AND '21:30:00' THEN '0900PM'
WHEN depart_tm BETWEEN '21:31:00' AND '22:00:00' THEN '0930PM'
WHEN depart_tm BETWEEN '22:01:00'AND '22:30:00' THEN '1000PM'
WHEN depart_tm BETWEEN '22:31:00' AND '23:00:00' THEN '1030PM'
WHEN depart_tm BETWEEN '23:01:00'AND '23:30:00' THEN '1100PM'
WHEN depart_tm BETWEEN '23:31:00' AND '23:59:00 'THEN '1130PM'
END AS timeinterval
FROM
Finallift
EG. output
 

Depart

Dest

Depart_tm

timeinterval

MSP

KEF

18:30:00

0600PM

MSP

AMS

18:30:00

0600PM

MSP

CLT

18:34:00

 

MSP

MSN

18:34:00

 

MSP

ORD

18:35:00

 

MSP

ALB

18:35:00

 

MSP

ATW

18:35:00

 

MSP

FNT

18:35:00

 

MSP

SBN

18:35:00

 

 
 
 
 

Raja_KT 1246 posts Joined 07/09
11 Nov 2013

Hi,

 

How about beyond 18:31 and 19:00?

Next time, if you dont mind, when you paste your result set, please put in a proper format so that it will be easy to see.

 

Cheers,

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

amerid 9 posts Joined 02/13
17 Jan 2014

It pulls everything except the time slot between 18:31 and 19:00.

sgarlapa 88 posts Joined 03/13
19 Jan 2014

Is there any extra space or special characters while checking this condition.(which is not visible)
Some times retyping the statment may solve these type of surprising issues. please try.

You must sign in to leave a comment.