oracle 基于商店开门关门时间两个日期时间之间的时间计算

gg0vcinb 于 2022-11-22 发布在 Oracle

关注(0)|答案(1)|浏览(130)

我在Oracle中有一个表，其中TEMP有商店开门和关门时间。

| COMPANY_ID | DAY_OF_WEEK | SHOP OPEN TIME | SHOP CLOSE TIME |

|------------|-------------|---------------------|---------------------|

| shop 1 | 1 | +00 10:00:00.000000 | +00 21:30:00.000000 |

| shop 1 | 2 | +00 10:00:00.000000 | +00 21:30:00.000000 |

| shop 1 | 3 | +00 10:00:00.000000 | +00 21:30:00.000000 |

| shop 1 | 4 | +00 10:00:00.000000 | +00 21:30:00.000000 |

| shop 1 | 5 | +00 10:00:00.000000 | +00 21:30:00.000000 |

| shop 1 | 6 | +00 10:00:00.000000 | +00 21:30:00.000000 |

| shop 1 | 7 | +00 11:00:00.000000 | +00 18:00:00.000000 |

| shop 2 | 1 | +00 10:30:00.000000 | +00 20:00:00.000000 |

| shop 2 | 2 | +00 09:30:00.000000 | +00 22:00:00.000000 |

| shop 2 | 3 | +00 09:30:00.000000 | +00 22:00:00.000000 |

| shop 2 | 4 | +00 09:30:00.000000 | +00 22:00:00.000000 |

| shop 2 | 5 | +00 11:00:00.000000 | +00 18:00:00.000000 |

| shop 2 | 6 | +00 09:30:00.000000 | +00 22:00:00.000000 |

| shop 2 | 7 | +00 09:00:00.000000 | +00 22:00:00.000000 |

第1天是星期六
我有另一个表来记录投诉，当顾客投诉临时工时，我们会解决问题并计算解决问题所需的时间。如果商店在那个时间关门，我们不会考虑时间。所以如果商店在周一09：00到22：00营业，那么晚上22：00到早上09：00的时间就不会考虑。
抱怨表：

| SHOP | COMPLAIN NUMBER | COMPLAIN DAY TIME | RESOLVE DAY TIME | TIME TAKEN TO RESOLVE in minutes |

|-------|-------------------|---------------------|-------------------|-----------------------------------|

| SHOP1 | 1 | 01/10/2022 08:20 | 01/10/2022 16:10 | 310 |

| SHOP1 | 2 | 01/10/2022 08:20 | 06/10/2022 09:50 |

我手动计算了投诉编号1，解决查询所需的时间为310分钟。2022年10月1日是星期六，商店营业时间为上午11：00，根据商店营业和关门时间，解决投诉所需的时间为5小时10分钟，即310分钟，如果当天是星期一，我们看到周一的营业时间为上午10点，则解决投诉所需的时间为370分钟。（不同商店在不同日期的营业和关门时间可能不同）
在这个例子中，投诉和问题是同一天发生的，所以投诉比较容易，但是
如果投诉持续4或5天，我们应该如何计算，因为每天的营业时间和关门时间都不一样，所以在第二种情况下，所需的时间应该是4550分钟
2010年1月-570起投诉全天开放，因为投诉记录在商店开业前（）
2010年2月-750起投诉全天开放
03/10-全天开放750例投诉
04/10-全天开放750例投诉
05/10-全天开放420例投诉
06/10-740投诉在商店关门前10分钟结束
总共是3980分钟，请暂停此问题目前在我们的队列中。当我们添加答案时，您将收到电子邮件通知

oracle

来源：https://stackoverflow.com/questions/74390941/time-calculation-between-two-date-time-based-on-shop-opening-and-closing-time

1条答案

按热度按时间

toe950271#

要获得与商店营业时间的重叠，您可以使用分层查询或递归CTE将每个投诉拆分为单独的几天，并计算等效天数（从周日的1到周六的6），如下所示：

with rcte (shop, complain_number, complain_day_time, resolve_day_time,
    active_day, active_day_of_week) as (
  select shop, complain_number, complain_day_time, resolve_day_time,
    trunc(complain_day_time), trunc(complain_day_time + 1) - trunc(complain_day_time + 1, 'IW') + 1
  from complaints
  union all
  select shop, complain_number, complain_day_time, resolve_day_time,
    active_day + 1, trunc(active_day + 2) - trunc(active_day + 2, 'IW') + 1
  from rcte
  where active_day < trunc(resolve_day_time)
)
select * from rcte
order by shop, complain_number, active_day

| 选购|投诉编号|投诉日时间|解析_天_时间|活动日|一周中的活动日|
| - -|- -|- -|- -|- -|- -|
| 商店1|一个|2022年10月1日08时20分|2022年10月1日16时10分|2022年10月1日上午10时00分|七个|
| 商店1| 2个|2022年10月1日08时20分|2022年10月6日21时20分|2022年10月1日上午10时00分|七个|
| 商店1| 2个|2022年10月1日08时20分|2022年10月6日21时20分|2022年10月2日上午10：00：00|一个|
| 商店1| 2个|2022年10月1日08时20分|2022年10月6日21时20分|2022年10月3日上午10时00分|2个|
| 商店1| 2个|2022年10月1日08时20分|2022年10月6日21时20分|2022年10月4日00：00：00|三个|
| 商店1| 2个|2022年10月1日08时20分|2022年10月6日21时20分|2022年10月5日上午10：00：00|四个|
| 商店1| 2个|2022年10月1日08时20分|2022年10月6日21时20分|2022年10月6日上午10：00：00|五个|
然后将这些天中的每一天加入到当天的商店时间中，并使用最小/最大来计算出该天的小时数，减去活动结束/开始时间，并将得到的天数乘以1440，以分钟为单位;然后总结每一个投诉的所有方面：

with rcte (shop, complain_number, complain_day_time, resolve_day_time,
    active_day, active_day_of_week) as (
  select shop, complain_number, complain_day_time, resolve_day_time,
    trunc(complain_day_time), trunc(complain_day_time + 1) - trunc(complain_day_time + 1, 'IW') + 1
  from complaints
  union all
  select shop, complain_number, complain_day_time, resolve_day_time,
    active_day + 1, trunc(active_day + 2) - trunc(active_day + 2, 'IW') + 1
  from rcte
  where active_day < trunc(resolve_day_time)
)
select c.shop, c.complain_number, c.complain_day_time, c.resolve_day_time,
  sum(round(1440 * (least(c.active_day + s.shop_close_time, c.resolve_day_time)
   - greatest(c.active_day + s.shop_open_time, c.complain_day_time)))) as active_mins
from rcte c
join shops s on s.company_id = c.shop
and s.day_of_week = c.active_day_of_week
group by c.shop, c.complain_number, c.complain_day_time, c.resolve_day_time
order by c.shop, c.complain_number

| 选购|投诉编号|投诉日时间|解析_天_时间|活动分钟数|
| - -|- -|- -|- -|- -|
| 商店1|一个|2022年10月1日08时20分|2022年10月1日16时10分|三百一十|
| 商店1| 2个|2022年10月1日08时20分|2022年10月6日21时20分|小行星3860|
fiddle也显示了中间计算。
我已经将第二个投诉的解决时间调整为21：20，因为该商店只营业到21：30，而您说“投诉在商店关门前10分钟结束”。这样该投诉的总时间为3860分钟-您似乎在计算中增加了一天，得到4550分钟。

赞(0）回复(0）举报 2022-11-22

我来回答

oracle 基于商店开门关门时间两个日期时间之间的时间计算

1条答案

相关问题

热门标签

最新问答

oracle 基于 商店 开门 关门 时间 两 个 日期 时间 之间 的 时间 计算

1条答案

相关问题

热门标签

最新问答

oracle 基于商店开门关门时间两个日期时间之间的时间计算