Oracle query exclude specific datetime to average data

Question

I have table WA_SO_TBL_BREAK_TIME to record break time.

PLANID_FK      | START_BREAK_TIME      | FINISH_BREAK_TIME     | STATUS
SO202009000003 | 9/22/2020 09:40:00 AM | 9/22/2020 10:45:00 AM | CLOSED

Then now I have table WA_SO_TBL_RUNNING_QUANTITY to record quantity data.

PLANID_FK       | QUANTITY | DATEADDED
SO202009000003  | 3.00     | 09/22/2020 11:48:23 AM
SO202009000003  | 3.00     | 09/22/2020 11:00:12 AM
SO202009000003  | 3.00     | 09/22/2020 10:59:17 AM
SO202009000003  | 3.00     | 09/22/2020 10:58:17 AM
SO202009000003  | 3.00     | 09/22/2020 10:57:18 AM
SO202009000003  | 3.00     | 09/22/2020 10:56:20 AM
SO202009000003  | 3.00     | 09/22/2020 10:54:15 AM
SO202009000003  | 3.00     | 09/22/2020 10:53:20 AM
SO202009000003  | 3.00     | 09/22/2020 10:49:15 AM
SO202009000003  | 1.00     | 09/22/2020 10:48:17 AM
SO202009000003  | 1.00     | 09/22/2020 10:47:23 AM
SO202009000003  | 1.00     | 09/22/2020 10:46:26 AM
SO202009000003  | 1.00     | 09/22/2020 10:45:32 AM
SO202009000003  | 1.00     | 09/22/2020 10:44:35 AM
SO202009000003  | 1.00     | 09/22/2020 09:42:30 AM
SO202009000003  | 1.00     | 09/22/2020 09:40:22 AM
SO202009000003  | 1.00     | 09/22/2020 09:39:26 AM

Now I need to get average of interval duration time of each DATEADDED from DESC to ASC.

The query is:

select PLANID_FK, 
   avg(diff)
from (
select PLANID_FK, 
       DATEADDED, 
       24 * 60 * 60 * (DATEADDED - lag(DATEADDED) over (partition by PLANID_FK order by DATEADDED)) as diff
from WA_SO_TBL_RUNNING_QUANTITY
WHERE
PLANID_FK = 'SO202009000003'
)
group by PLANID_FK
order by PLANID_FK

The query is OK, but now I need to exclude query with data breaktime from this time: 9/22/2020 09:40:00 until 9/22/2020 10:45:00 AM. So on that time range will not be average.

Is it possible to do in the query?

user_13878023 · Answer

We can do left join and then filter it in the where clause should exclude the range falling between break time,

select planid_fk, avg(diff)
from 
(
 select q.planid_fk, 
        dateadded, b.start_break_time,b.finish_break_time,
        24 * 60 * 60 * (dateadded - lag(dateadded) over (partition by q.planid_fk order by dateadded)) as diff
   from wa_so_tbl_running_quantity q
   left join wa_so_tbl_break_time b
     on q.planid_fk = b.planid_fk
where q.planid_fk = 'SO202009000003'
  and  b.planid_fk is null
     or 
     (    b.planid_fk is not null 
       and q.dateadded not between b.start_break_time and b.finish_break_time
     )
)
group by planid_fk
order by planid_fk

Oracle query exclude specific datetime to average data

1 Answers