CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10gdate-arithmetic
HardLeeWorking
HardLeeWorking

Reputation: 195

Calculate time difference ignorning non-work hours

I am trying to calculate the difference between 2 datetime values where non-work hours are ignored. Originally it just looked at the difference and calculated it as minutes however It needs to count only hours between 9am and 8pm Monday to Friday and 9am - 1pm Saturday, ignoring all other times. I am on an oracle 10g system.

my code as it currently stands is as follows:

 begin 

 debug.debug('sp_access');

update cl_case b
    set time_to_sp_access = 
    (
    select (x.date_created-e.date_created)*1440
    from cl_case c, eventlog e, eventlog x
    where c.id=e.case_id
    and x.case_id=e.case_id
    and b.id=e.case_id
    and e.id=
                 (    select min(id) from eventlog mini
                      where mini.case_id=e.case_id
                      and mini.cl_code in ('AAAA','BBBB','CCCC','DDDD')
                 )
    and x.id=
                 (     select min(id) from eventlog minix
                       where minix.case_id=e.case_id
                       and minix.cl_code in  ('EEEE','FFF','GGG','HHHH','JJJJ','KKKK','LLLL')
                 )
    )
where id in 
    (    select unique case_id 
        from   eventlog elog
        where  elog.sptime_needs_setting ='Y'
    );

    commit;

end sp_access;

How can I get this to count time between specified hours?

thanks

Upvotes: 1

Views: 118

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You could use a CASE expression in the WHERE clause. Since there are two datetime values, you need to use two case expressions.

For example, the CASE expression would evaluate as:

SQL> SELECT
  2    CASE
  3      WHEN TO_CHAR(SYSDATE, 'DY') BETWEEN '1' AND '5'
  4      THEN TO_DATE(TO_CHAR(TRUNC(SYSDATE), 'MM/DD/YYYY')
  5        ||' 08:00:00 PM', 'MM/DD/YYYY HH:MI:SS PM')
  6      ELSE TO_DATE(TO_CHAR(TRUNC(SYSDATE), 'MM/DD/YYYY')
  7        ||' 01:00:00 PM', 'MM/DD/YYYY HH:MI:SS PM')
  8    END my_time
  9  FROM dual;

MY_TIME
----------------------
11/24/2015 01:00:00 pm

The above example check the DAY for SYSDATE, and depending on it returns a datetime value.

using the above example, since you have two different datetime values to be compared as a date range condition, you will need two CASE expressions in your WHERE clause.

WHERE date_column 
BETWEEN
   CASE
      WHEN TO_CHAR(date_column, 'DY') BETWEEN '1' AND '5' 
      THEN
         TO_DATE(TO_CHAR(
                 TRUNC(date_column), 'MM/DD/YYYY') 
                   ||' 09:00:00 AM', 'MM/DD/YYYY HH:MI:SS PM')
      ELSE
         TO_DATE(TO_CHAR(
                 TRUNC(date_column), 'MM/DD/YYYY') 
                   ||' 09:00:00 AM', 'MM/DD/YYYY HH:MI:SS PM')
   END 
AND
   CASE
      WHEN TO_CHAR(date_column, 'DY') BETWEEN '1' AND '5' 
      THEN
         TO_DATE(TO_CHAR(
                 TRUNC(date_column), 'MM/DD/YYYY') 
                    ||' 08:00:00 PM', 'MM/DD/YYYY HH:MI:SS PM')
      ELSE
         TO_DATE(TO_CHAR(
                 TRUNC(date_column), 'MM/DD/YYYY') 
                    ||' 01:00:00 PM', 'MM/DD/YYYY HH:MI:SS PM')
   END

Upvotes: 1

Related Questions