How to write a date & time shift query?

Question

Can someone please help me with writing a CASE query. Using the column name PERFORM_DT_TM - date & time format "MM-DD-YYYY HH-MI-SS"

Table name PERFORM_RESULT

The result I want is something like this:

BETWEEN'06:00:00'AND'14:29:59' THEN 'First Shift'
BETWEEN'14:30:00'AND'22:59:59' THEN 'Second Shift'
BETWEEN '23:00:00' AND'05:59:59' THEN 'Third Shift'
ELSE 'UNKNOWN'

Answer 1

Assuming PERFORM_DT_TM is in datetime datatype (as it should be), you don't need a format model like 'MM-DD-YYYY' or similar; and you are best off using numeric comparisons rather than lexicographic.

The time of day is PERFORM_DT_TM - trunc(PERFORM_DT_TM). trunc keeps the same date but truncates the time portion back to 00:00:00 AM, so the difference is just the time component of the datetime stored in PERFORM_DT_TM. However, this "time component" is expressed as a fraction of 1 day; to compare to hours, divide the hours by 24 (or alternatively, multiply the time differences by 24; I illustrate the first choice below).

Assuming your table name is MY_TABLE, your case expression can be written (using the "with clause" available in Oracle 11.1 and above) as follows:

with p as (
        select PERFORM_DT_TM - trunc(PERFORM_DT_TM) as PERF_TM [, other columns] 
        from MY_TABLE
     )
select [other columns, perhaps PERF_TM,]
       case
       when PERF_TM >=    6/24 and PERF_TM <  14.5/24 then "First Shift"
       when PERF_TM >= 14.5/24 and PERF_TM <    23/24 then "Second Shift"
       when PERF_TM <     6/24  or PERF_TM >=   23/24 then "Third Shift"
       else                                                "Error/Unknown"
       end as SHIFT
from p;

Answer 2

Oracle Setup:

CREATE TABLE PERFORM_RESULT ( PERFORM_DT_TM DATE );

INSERT INTO PERFORM_RESULT
SELECT TIMESTAMP '2016-05-23 00:00:00' FROM DUAL UNION ALL
SELECT TIMESTAMP '2016-05-21 12:00:00' FROM DUAL UNION ALL
SELECT TIMESTAMP '2016-05-19 15:00:00' FROM DUAL;

Query 1 - Comparing using fractions of a day:

SELECT PERFORM_DT_TM,
       CASE WHEN time >= 6/24 AND time < 14.5/24  THEN 'First Shift'
            WHEN time >= 14.5/24 AND time < 23/24 THEN 'Second Shift'
                                                  ELSE 'Third Shift'
            END AS shift
FROM   (
  SELECT PERFORM_DT_TM,
         PERFORM_DT_TM - TRUNC( PERFORM_DT_TM ) AS time
  FROM   PERFORM_RESULT
);

Query 2 - Comparing using intervals:

SELECT PERFORM_DT_TM,
       CASE WHEN time >= INTERVAL  '6' HOUR
            AND  time <  INTERVAL '14' HOUR + INTERVAL '30' MINUTE
            THEN 'First Shift'
            WHEN time >= INTERVAL '14' HOUR + INTERVAL '30' MINUTE
            AND  time <  INTERVAL '23' HOUR
            THEN 'Second Shift'
            ELSE 'Third Shift'
            END AS shift
FROM   (
  SELECT PERFORM_DT_TM,
         NUMTODSINTERVAL( PERFORM_DT_TM - TRUNC( PERFORM_DT_TM ), 'DAY' ) AS time
  FROM   PERFORM_RESULT
);

(or you could just convert all the times to minute intervals - i.e. INTERVAL '360' MINUTE, INTERVAL '870' MINUTE and INTERVAL '1380' MINUTE as the boundaries)

Output:

(Both output the same results)

PERFORM_DT_TM       SHIFT      
------------------- ------------
2016-05-23 00:00:00 Third Shift  
2016-05-21 12:00:00 First Shift  
2016-05-19 15:00:00 Second Shift 

Answer 3

Assuming this is for Oracle, and assuming that the PERFORM_DT_TM column is datatype DATE...

We can use the TO_CHAR function to format the time portion. For example:

TO_CHAR( t.perform_dt_tm,'HH24:MI' )

And we can do string comparisons in a CASE expression. For example:

SELECT CASE
         WHEN TO_CHAR(t.perform_dt_tm,'HH24:MI') >= '06:00' 
          AND TO_CHAR(t.perform_dt_tm,'HH24:MI')  < '14:30'
         THEN 'First Shift'
         WHEN TO_CHAR(t.perform_dt_tm,'HH24:MI') >= '14:30'
          AND TO_CHAR(t.perform_dt_tm,'HH24:MI')  < '23:00'
         THEN 'Second Shift'
         WHEN TO_CHAR(t.perform_dt_tm,'HH24:MI') >= '23:00'
           OR TO_CHAR(t.perform_dt_tm,'HH24:MI')  < '06:00'
         THEN 'Third Shift'
         ELSE 'Unknown'
       END AS which_shift
     , t.perform_dt_tm
     , ...
  FROM ... t

---

NOTE: This is based on the assumption that datatype of the column is DATE, and that this is for Oracle. If the column is a different datatype, then we'd need to adjust how the "time" portion is extracted.

The TO_CHAR function is specific to Oracle. If this is for a database other than Oracle, we would need to adjust the expression that extracts the time portion. (e.g. DATE_FORMAT for MySQL)

---

An equivalent result can be obtained more concisely:

SELECT CASE
         WHEN TO_CHAR(t.perform_dt_tm,'HH24')     < '06:00'
         THEN 'Third Shift'
         WHEN TO_CHAR(t.perform_dt_tm,'HH24:MI')  < '14:30'
         THEN 'First Shift'
         WHEN TO_CHAR(t.perform_dt_tm,'HH24')     < '23:00'
         THEN 'Second Shift'
         WHEN TO_CHAR(t.perform_dt_tm,'HH24')     < '24:00'
         THEN 'Third Shift'
         ELSE 'Unknown'
       END AS which_shift
     , t.perform_dt_tm
     , ...
  FROM ... t

Answer 4

You can use to_char(PERFORM_RESULT, 'HH24:MI') to get the hours and minutes from the date; the seconds aren't needed for this.

For 23:00 to 05:59 you need to check two ranges, because BETWEEN doesn't know about clock wraparound, it's just doing a textual comparison.

SELECT CASE
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') BETWEEN '06:00' AND '14:29' THEN 'First Shift'
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') BETWEEN '14:30' AND '22:59' THEN 'Second Shift'
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') BETWEEN '23:00' AND '23:59' THEN 'Third Shift'
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') BETWEEN '00:00' AND '05:59' THEN 'Third Shift'
    ELSE 'UNKNOWN'
END

You can also take advantage of the fact that cases are tested sequentially to simplify it, since you don't have to check the beginning of a range if that has been excluded by the previous case.

SELECT CASE
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') <= '05:59' THEN 'Third Shift'    
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') <= '14:29' THEN 'First Shift'
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') <= '22:59' THEN 'Second Shift'
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') <= '23:59' THEN 'Third Shift'
    ELSE 'UNKNOWN'
END