Case statement not pulling time between 18:31:01 and 19:00:00

Database
Enthusiast

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  
Tags (2)
3 REPLIES
Enthusiast

Re: Case statement not pulling time between 18:31:01 and 19:00:00

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

Enthusiast

Re: Case statement not pulling time between 18:31:01 and 19:00:00

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

Enthusiast

Re: Case statement not pulling time between 18:31:01 and 19:00:00

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.