时间戳

首页 > 解决方案 > 如何使用 sql 找到两个日期之间有可用工作时间的团队？

sql - 如何使用 sql 找到两个日期之间有可用工作时间的团队？

问题描述

我想将工作分配给团队。在此，我需要免费的团队的日期和时间信息。但我无法用 SQL 拉它。在下面的图片中，我已经指出了表结构以及我想要得到的结果。在此先感谢您的帮助。

表 1：工作日期

ID  TEAM_NO     START_DATE          END_DATE
-----------------------------------------------------
1   A1          20.08.2021 13:00    20.08.2021 18:00
2   B1          19.08.2021 08:00    22.08.2021 18:00
3   G1          25.08.2021 08:00    25.08.2021 18:00
4   A2          17.08.2021 08:00    17.08.2021 18:00
5   A1          16.08.2021 08:00    16.08.2021 12:00

表 2：团队

ID  TEAM_NO     TEAM_NAME
--------------------------
1   A1          ALPHA1
2   A2          ALPHA2
3   B1          BETA1
4   B2          BETA2
5   G1          GAMMA1

查询：两个日期之间

START_DATE          END_DATE
-------------------------------------
20.08.2021 08:00    20.08.2021 18:00

结果

TEAM_NO     FREE_DATETIME_START     FREE_DATETIME_END
----------------------------------------------------------
A1          20.08.2021 08:00        20.08.2021 13:00
G1          20.08.2021 08:00        20.08.2021 18:00
A2          20.08.2021 08:00        20.08.2021 18:00

工作日期

1

团队

2

询问

3

结果

4

标签： sqloracleoracle12c

解决方案

这是典型的差距和孤岛问题。我给你一个方法来解决你的请求

with ranges (HOURS) as (
  select TO_DATE('20.08.2021 08:00:00','DD.MM.YYYY HH24:MI:SS') from dual
  union all
  select HOURS + interval '1' hour from ranges where HOURS < TO_DATE('20.08.2021 18:00:00','DD.MM.YYYY HH24:MI:SS')
)
select
  TO_CHAR(min(r.HOURS),'YYYY/MM/DD HH24:MM') as FREE_START_DATE, 
  TO_CHAR(max(r.HOURS),'YYYY/MM/DD HH24:MM') as FREE_END_DATE
from ranges r
  cross join WORKING_DATES w
where case when r.HOURS >= w.START_DATE and r.HOURS <  w.END_DATE then 'Y' else 'N' end = 'N'
  and w.START_DATE >= TO_DATE('20.08.2021 08:00:00','DD.MM.YYYY HH24:MI:SS')
group by TEAM_NO

首先，我创建了一个递归 CTE ( ranges)，它人为地在您想要的“两个日期之间”之间创建了几个小时。

然后，我cross join在您的WORKING_DATES表和range. 这使我们能够检查每个小时是否在每个寄存器的START_DATE和之间END_DATE。我在声明中写了这个过滤器是case when为了让你更好地理解它的含义：'Y'意思是“工作”，'N'意思是空闲时间。

此外，我添加了一个过滤器来跳过所有那些START_DATE在您感兴趣的区间之前的行。

推荐阅读