Using case when with a timestamp in the select statement

Database

Using case when with a timestamp in the select statement

Hello, I'm looking at clinic data and I'd like to group my check-in times into three main categories: 1) 7am-4pm, 2) 4:01pm-6pm, and 3) 6:01pm-11:59pm. Check-in time is formatted as follows: mm/dd/yyyy hh:mm:ss, and is 24-hour. But I reformatted it like this: hh:mi:ssbt. So now it gives me a 12-hour time with an AM/PM designation. This is my code, which doesn't seem to be working:

SELECT

PATIENT.PAT_NAME

,PAT_ENC.CHECKIN_TIME (FORMAT 'hh:mi:ssbt') (CHAR(12)) AS CHECKIN

,CASE WHEN CHECKIN BETWEEN '07:00:00 AM' AND '04:00:59 PM' THEN 1

      WHEN CHECKIN BETWEEN '04:01:00 PM' AND '06:00:59 PM' THEN 2

      WHEN CHECKIN BETWEEN '06:01:00 PM' AND '11:59:59 PM' THEN 3

ELSE 0 END AS TIME_GROUPS

I only get zeroes in my time_groups column. Any help would be much appreciated! Thank you!

3 REPLIES
Senior Apprentice

Re: Using case when with a timestamp in the select statement

When you cast a time to a string you apply string comparison rules (and a string doesn't care about AM/PM)

Simply keep the time:

SELECT
PATIENT.PAT_NAME
,PAT_ENC.CHECKIN_TIME (FORMAT 'hh:mi:ssbt') (CHAR(12)) AS CHECKIN
,CASE WHEN CHECKIN_TIME BETWEEN TIME '07:00:00' AND TIME '16:00:59' THEN 1
WHEN CHECKIN_TIME BETWEEN TIME '16:01:00' AND TIME '18:00:59' THEN 2
WHEN CHECKIN_TIME BETWEEN TIME '18:01:00' AND '23:59:59' THEN 3
ELSE 0 END AS TIME_GROUPS

Re: Using case when with a timestamp in the select statement

Hi Dieter,

Thank you so much for your feedback! Actually, I get an Invalid operation for DateTime or Interval (error 5407) message when I submit your code. Using checkin as opposed to checkin_time in the case statement fixed that, but I'm still getting inappropriate groupings. Here's my revised code and below that, the results

SELECT TOP 10
PATIENT.PAT_NAME
,PAT_ENC.CHECKIN_TIME (FORMAT 'hh:mi:ssbt') (CHAR(12)) AS CHECKIN
,CASE WHEN CHECKIN IS NULL THEN 0
WHEN CHECKIN BETWEEN TIME '07:00:00' AND TIME '16:00:59' THEN 1
WHEN CHECKIN BETWEEN TIME '16:01:00' AND TIME '18:00:59' THEN 2
ELSE 3 END AS TIME_GROUPS
CHECKIN TIME_GROUPS
8:55:09 AM 1
9:29:31 AM 1
10:24:29 AM 1
8:47:53 AM 1
2:24:30 PM 3
3:51:08 PM 3
8:53:01 AM 1
4:06:22 PM 3
6:03:11 PM 3
6:11:19 PM 3

Thanks for taking another look!

Senior Apprentice

Re: Using case when with a timestamp in the select statement

What's the datatype of CHECKIN_TIME?

SELECT
CURRENT_TIME AS CHECKIN_TIME
,CHECKIN_TIME (FORMAT 'hh:mi:ssbt') (CHAR(12)) AS CHECKIN
,CASE WHEN CHECKIN_TIME BETWEEN TIME '07:00:00' AND TIME '16:00:59' THEN 1
WHEN CHECKIN_TIME BETWEEN TIME '16:01:00' AND TIME '18:00:59' THEN 2
WHEN CHECKIN_TIME BETWEEN TIME '18:01:00' AND '23:59:59' THEN 3
ELSE 0
END AS TIME_GROUPS

CHECKIN_TIME CHECKIN TIME_GROUPS
09:57:53+00:00 09:57:53 AM 1