Forum Stats

  • 3,827,794 Users
  • 2,260,821 Discussions
  • 7,897,376 Comments

Discussions

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

gg
gg Member Posts: 150 Bronze Badge

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

Tagged:

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy

    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
    
    gg
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    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;
    


    gg
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,488 Red Diamond

    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.

    BluShadow
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,488 Red Diamond

    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
    Frank Kulash Member, Moderator Posts: 42,073 Red Diamond

    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
    ;
    


    gg
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,488 Red Diamond

    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
  • gg
    gg Member Posts: 150 Bronze Badge

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

    Much appreciated.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,488 Red Diamond

    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.