Can we convert this case statement to decode to calculate the shift timings in oracle sql?

gg

I need to compute the shift timings based on the below time range 6 AM - 1:59:59 PM as AM shift 2 PM - 9:59:59 PM as PM shift 10 PM - 5:59:59 AM as NTS shift else null

Can some one help me to do the below with decode statement in Oracle 19C?

SELECT 
CASE WHEN to_char(wtc2.actual_end_datetime, 'hh24:mi:ss') BETWEEN '06:00:00' AND '13:59:59' THEN  'AM'
            WHEN to_char(wtc2.actual_end_datetime, 'hh24:mi:ss') BETWEEN '14:00:00' AND '21:59:59' THEN 'PM'
            WHEN to_char(wtc2.actual_end_datetime, 'hh24:mi:ss') > '21:59:59' OR 
                 to_char(wtc2.actual_end_datetime, 'hh24:mi:ss') < '06:00:00' THEN 'NTS'
            ELSE NULL
            END dropoff_shift
FROM TAB1 wtc2

Thanks

Gautam

User_H3J7U

with decode

You can use the trunc of shifted time*3 to get decode index.

select dt,
  coalesce(nullif(nullif(to_char(dt+02/24, 'AM'), to_char(dt+10/24, 'AM')), 'AM'),
           nullif(nullif(to_char(dt-02/24, 'AM'), to_char(dt+06/24, 'AM')), 'PM'),
           'NTS') cnn,
  CASE WHEN to_char(dt, 'hh24:mi:ss') BETWEEN '06:00:00' AND '13:59:59' THEN  'AM'
            WHEN to_char(dt, 'hh24:mi:ss') BETWEEN '14:00:00' AND '21:59:59' THEN 'PM'
            WHEN to_char(dt, 'hh24:mi:ss') > '21:59:59' OR
                 to_char(dt, 'hh24:mi:ss') < '06:00:00' THEN 'NTS'
            ELSE NULL
            END h0
from (select trunc(sysdate)+level/24 dt from dual connect by level<30);

DT                  CNN H0
------------------- --- ---
2022-01-26 01:00:00 NTS NTS
2022-01-26 02:00:00 NTS NTS
2022-01-26 03:00:00 NTS NTS
2022-01-26 04:00:00 NTS NTS
2022-01-26 05:00:00 NTS NTS
2022-01-26 06:00:00 AM  AM
2022-01-26 07:00:00 AM  AM
2022-01-26 08:00:00 AM  AM
2022-01-26 09:00:00 AM  AM
2022-01-26 10:00:00 AM  AM
2022-01-26 11:00:00 AM  AM
2022-01-26 12:00:00 AM  AM
2022-01-26 13:00:00 AM  AM
2022-01-26 14:00:00 PM  PM
2022-01-26 15:00:00 PM  PM
2022-01-26 16:00:00 PM  PM
2022-01-26 17:00:00 PM  PM
2022-01-26 18:00:00 PM  PM
2022-01-26 19:00:00 PM  PM
2022-01-26 20:00:00 PM  PM
2022-01-26 21:00:00 PM  PM
2022-01-26 22:00:00 NTS NTS
2022-01-26 23:00:00 NTS NTS
2022-01-27 00:00:00 NTS NTS
2022-01-27 01:00:00 NTS NTS
2022-01-27 02:00:00 NTS NTS
2022-01-27 03:00:00 NTS NTS
2022-01-27 04:00:00 NTS NTS
2022-01-27 05:00:00 NTS NTS

BluShadow

https://community.oracle.com/tech/developers/discussion/4493944/can-we-convert-this-case-statement-to-decode-to-calculate-the-shift-timings-in-oracle-sql

Because all your shifts are based on start and end times that are on the hour, you can just check the hours of the date/time for the range...

with t(dt) as (select trunc(sysdate)+level/48 from dual connect by level<60)
--
select dt
      ,case when to_number(to_char(dt,'HH24')) between 6 and 13 then 'AM'
            when to_number(to_char(dt,'HH24')) between 14 and 21 then 'PM'
            when to_number(to_char(dt,'HH24')) >= 22
              or to_number(to_char(dt,'HH24')) < 6 then 'NTS'
       else null
       end dropoff_shift
from   t;

Solomon Yakobson

Or simpler:

with t(dt) as (select trunc(sysdate)+level/48 from dual connect by level<60)
--
select dt
      ,case when to_number(to_char(dt,'HH24')) between 6 and 13 then 'AM'
            when to_number(to_char(dt,'HH24')) between 14 and 21 then 'PM'
            when dt is not null then 'NTS'
       end dropoff_shift
from   t;

SY.

Solomon Yakobson

Or:

with t(dt) as (select trunc(sysdate)+level/48 from dual connect by level<60)
--
select dt,
       case
         when to_number(to_char(dt + 1 / 12,'HH24')) < 8 then 'NTS'
         when to_number(to_char(dt + 1 / 12,'HH24')) < 16 then 'AM'
         when dt is not null then 'PM'
       end dropoff_shift
from   t
/

SY.

Frank Kulash

Hi, @gg

Can some one help me to do the below with decode statement in Oracle 19C?

Why DECODE? A CASE experession will be just as efficient, and a lot easier to debug and maintain.

If you really, really must use DECODE, here's one way:

SELECT   actual_end_datetime
,	 DECODE ( FLOOR ( 3 * ( actual_end_datetime + (2 / 24)
	 	    	      - TRUNC (actual_end_datetime + (2 / 24))
			      )
			 )
		 , 0, 'NTS'
		 , 1, 'AM'
		 , 2, 'PM'
		 ) AS dropoff_shift
FROM	 tab1
;

Solomon Yakobson

Or:

with t(dt) as (select trunc(sysdate)+level/48 from dual connect by level<60)
--
select dt,
       case trunc(to_number(to_char(dt + 1 / 12,'HH24')) / 8)
         when 0 then 'NTS'
         when 1 then 'AM'
         when 2 then 'PM'
       end dropoff_shift
from   t
/

And using DECODE:

with t(dt) as (select trunc(sysdate)+level/48 from dual connect by level<60)
--
select dt,
       decode(
              trunc(to_number(to_char(dt + 1 / 12,'HH24')) / 8),
              0,'NTS',
              1,'AM',
              2,'PM'
             ) dropoff_shift
from   t
/

SY.

gg

Thanks for the simplified solutions User_H3J7U, Blushadow, Solomon and Frank.

Much appreciated.

Solomon Yakobson

And if date is always not null you can "tune" it a bit more by changing last WHEN to ELSE (last pair to else value in DECODE):

with t(dt) as (select trunc(sysdate)+level/48 from dual connect by level<60)
--
select dt,
       case trunc(to_number(to_char(dt + 1 / 12,'HH24')) / 8)
         when 0 then 'NTS'
         when 1 then 'AM'
         else 'PM'
       end dropoff_shift
from   t
/

And using DECODE:

with t(dt) as (select trunc(sysdate)+level/48 from dual connect by level<60)
--
select dt,
       decode(
              trunc(to_number(to_char(dt + 1 / 12,'HH24')) / 8),
              0,'NTS',
              1,'AM',
                'PM'
             ) dropoff_shift
from   t
/

SY.