time difference between two date removing closing time

deepankar srigyan 0
Viewed 25

my company has numbers of shops around all the locations. They raised a request for delivering the item to their shop which they can sell . We wanted to understand how much time the company takes to deliver the item in minutes.However, we don't want to add the time in our elapsed time when the shop is closed i.e.

lets consider shop opening and closing time are

enter image description here

now elapsed time

enter image description here

When I deduct complain time and resolution time then I get calculatable elasped time in minutes but I need Required elapsed time in minutes so in the first case out of 2090 minutes those minutes are deducated when shop was closed. I need to write an oracle query to calcualted the required elapsed time in minutes which is in green.

help what query we can write.

oracle-sqldeveloper oracle
1 Answers

One formula to get the net time is as follows:

  • For every day involved add up the opening times. For your first example this is two days 2021-01-11 and 2021-01-12 with 13 daily opening hours (09:00 - 22:00). That makes 26 hours.
  • If the first day starts after the store opens, subtract the difference. 10:12 - 09:00 = 1:12 = 72 minutes.
  • If the last day ends before the store closes, subtract the difference. 22:00 - 21:02 = 0:58 = 58 minutes.

Oracle doesn't have a TIME datatype, so I assume you are using Oracle's datetime data type they call DATE to store the opening and closing time and we must ignore the date part. And you are probably using the DATE type for the complain_time and the resolution_time, too.

In below query I convert the time parts to minutes right away, so the calculations get a tad more readable later.

with s as
(
  select
    shop,
    extract(hour from opening_time) * 60 + extract(minute from opening_time) as opening_minute,
    extract(hour from closing_time) * 60 + extract(minute from closing_time) as closing_minute
  from shops
)
, r as
(
  select 
    request, shop, complain_time, resolution_time,
    trunc(complain_time) as complain_day,
    trunc(resolution_time) as resolution_day,
    extract(hour from complain_time) * 60 + extract(minute from complain_time) as complain_minute,
    extract(hour from resolution_time) * 60 + extract(minute from resolution_time) as resolution_minute
  from requests
)
select
  r.request, r.shop, r.complain_time, r.resolution_time,
  (r.resolution_day - r.complain_day + 1) * 60
  - case when r.complain_minute > s.opening_minute) then r.complain_minute - s.opening_minute else 0 end
  - case when r.resolution_minute < s.opening_minute) then s.closing_minute - r.resolution_minute else 0 end
    as net_duration_in_minutes
  from r
join s on s.shop = r.shop
order by r.request;
Thorsten Kettner0
Related