All Forums Database
valks97 2 posts Joined 06/15
23 Jun 2015
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!

dnoeth 4628 posts Joined 11/04
23 Jun 2015

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

 

Dieter

valks97 2 posts Joined 06/15
26 Jun 2015

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!

dnoeth 4628 posts Joined 11/04
27 Jun 2015

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

 

Dieter

You must sign in to leave a comment.