时间戳

首页 > 解决方案 > 确定时间范围内值班的工人

sql-server - 确定时间范围内值班的工人

问题描述

我有以下数据（简化）：

PersonID 开始时间 完成时间 工作时间
个人 1 2020-07-13T07:00:23 2020-07-13T14:30:23 7.30
人 2 2020-07-13T22:00:12 2020-07-14T07:10:28 9.10

请注意，第 2 个人是一名夜间工人，从下午 13 点开始到上午 14 点，白天的工人在半小时结束。

我需要制作一个表格，显示：

PersonID 开始 结束 12-2am 2-4am 4-6am 6-8am 8-10am
人 2 2020-07-13T22:00:12 2020-07-14T07:10:28 2 2 2 1 0

同一张表还显示了进一步的例子：

PersonID 开始 结束 6-8am 8-10am 10-12pm 12-2pm 2-4pm
人 1 2020-07-13T07:00:23 2020-07-13T14:30:23 2 2 2 1 0.5

我的想法是，然后我可以运行一个查询，计算该 24 小时期间的总小时数，但请注意夜间工作人员将在一天中工作 2 小时，下一天工作 7 小时。到目前为止，我已经尝试做一个实质性的案例陈述来涵盖两个小时的时段，但是虽然我知道出了什么问题（它只计算第一个间隔 - 例如从 07:00 开始，所以填充了 6-8 的时间范围，并且也只放一个 1 在那里，不管那个槽里有 1 小时还是 2 小时），我不知道如何到达上面的桌子。我的 SQL 技能还可以，但肯定不如这个论坛的大多数成员。我目前的代码（忽略我糟糕的格式）是：

select concat(s.firstname, ' ',s.lastname) as "Worker", left(wt.[started],19) as "Started", left(wt.[finished],19) as "Finished", 

COUNT(CASE WHEN right(left(wt.[started],19),8) between '00:00:00' and '02:00:00' THEN 1 END) "0000-0200",
COUNT(CASE WHEN right(left(wt.[started],19),8) between '02:00:00' and '04:00:00' THEN 1 END) "0200-0400",
COUNT(CASE WHEN right(left(wt.[started],19),8) between '04:00:00' and '06:00:00' THEN 1 END) "0400-0600",
COUNT(CASE WHEN right(left(wt.[started],19),8) between '06:00:00' and '08:00:00' THEN 1 END) "0600-0800",
COUNT(CASE WHEN right(left(wt.[started],19),8) between '08:00:00' and '10:00:00' THEN 1 END) "0800-1000",
COUNT(CASE WHEN right(left(wt.[started],19),8) between '10:00:00' and '12:00:00' THEN 1 END) "1000-1200",
COUNT(CASE WHEN right(left(wt.[started],19),8) between '12:00:00' and '14:00:00' THEN 1 END) "1200-1400",
COUNT(CASE WHEN right(left(wt.[started],19),8) between '14:00:00' and '16:00:00' THEN 1 END) "1400-1600",
COUNT(CASE WHEN right(left(wt.[started],19),8) between '16:00:00' and '18:00:00' THEN 1 END) "1600-1800",
COUNT(CASE WHEN right(left(wt.[started],19),8) between '18:00:00' and '20:00:00' THEN 1 END) "1800-2000",
COUNT(CASE WHEN right(left(wt.[started],19),8) between '20:00:00' and '22:00:00' THEN 1 END) "2000-2200",
COUNT(CASE WHEN right(left(wt.[started],19),8) between '22:00:00' and '23:59:59' THEN 1 END) "2200-2359"


from [workedtime] wt
inner join [staff] s on s.personid = wt.personid
group by concat(s.firstname, ' ',s.lastname), wt.[started], wt.finished

有没有人有任何可以帮助的建议或想法？

标签： sql-server

解决方案

当您将此数据加载到 Power BI（或任何与此相关的报告工具）中时，您不希望或过早地将您的数据转换为一堆列，因为这会使您的报告变得更加困难。相反，您希望使输出保持良好和分类，因此一列可以用作表格和图表的分组。

像下面的脚本这样的东西会为你做到这一点，尽管我并不认为你正在以最好的方式处理整个情况。

理想情况下，您将使用星型架构构建您的 Power BI 数据模型，并包含一个日期和时间维度，然后您可以将其用作类别组。然后，您的事实数据（工作小时数）将在他们轮班的每个小时内加载一行，其中包含您需要报告的相关指标。

SQL

declare @d table(PersonID varchar(10),Started datetime,Finished datetime,HoursWorked decimal(10,5));
insert into @d values
 ('Person1','2020-07-13T07:00:23','2020-07-13T14:30:23',7.30)
,('Person2','2020-07-13T22:00:12','2020-07-14T07:10:28',9.10);

declare @StartDate datetime = '2020-07-13',
        @EndDate   datetime = '2020-07-14';

select d.PersonID
      ,d.Started
      ,d.Finished
      ,cast(datepart(hour,t.s) as varchar(2)) + '-' + cast(datepart(hour,t.f) as varchar(2)) as HourPeriod
      ,datediff(minute,case when d.Started < t.s then t.s else d.Started end, case when d.Finished > t.f then t.f else d.Finished end) / 60. as HoursWorked
from @d as d
    cross apply(values(dateadd(hour,0,d.Started) ,dateadd(hour,2,d.Started))
                     ,(dateadd(hour,2,d.Started) ,dateadd(hour,4,d.Started))
                     ,(dateadd(hour,4,d.Started) ,dateadd(hour,6,d.Started))
                     ,(dateadd(hour,6,d.Started) ,dateadd(hour,8,d.Started))
                     ,(dateadd(hour,8,d.Started) ,dateadd(hour,10,d.Started))
                     ,(dateadd(hour,10,d.Started),dateadd(hour,12,d.Started))
                     ,(dateadd(hour,12,d.Started),dateadd(hour,14,d.Started))
                     ,(dateadd(hour,14,d.Started),dateadd(hour,16,d.Started))
                     ,(dateadd(hour,16,d.Started),dateadd(hour,18,d.Started))
                     ,(dateadd(hour,18,d.Started),dateadd(hour,10,d.Started))
                     ,(dateadd(hour,20,d.Started),dateadd(hour,22,d.Started))
                     ,(dateadd(hour,22,d.Started),dateadd(hour,24,d.Started))
               ) as t(s,f)
where d.Started <= t.f
    and d.Finished > t.s
order by d.PersonID
        ,t.s;

输出

+----------+-------------------------+-------------------------+------------+-------------+
| PersonID |         Started         |        Finished         | HourPeriod | HoursWorked |
+----------+-------------------------+-------------------------+------------+-------------+
| Person1  | 2020-07-13 07:00:23.000 | 2020-07-13 14:30:23.000 | 7-9        |    2.000000 |
| Person1  | 2020-07-13 07:00:23.000 | 2020-07-13 14:30:23.000 | 9-11       |    2.000000 |
| Person1  | 2020-07-13 07:00:23.000 | 2020-07-13 14:30:23.000 | 11-13      |    2.000000 |
| Person1  | 2020-07-13 07:00:23.000 | 2020-07-13 14:30:23.000 | 13-15      |    1.500000 |
| Person2  | 2020-07-13 22:00:12.000 | 2020-07-14 07:10:28.000 | 22-0       |    2.000000 |
| Person2  | 2020-07-13 22:00:12.000 | 2020-07-14 07:10:28.000 | 0-2        |    2.000000 |
| Person2  | 2020-07-13 22:00:12.000 | 2020-07-14 07:10:28.000 | 2-4        |    2.000000 |
| Person2  | 2020-07-13 22:00:12.000 | 2020-07-14 07:10:28.000 | 4-6        |    2.000000 |
| Person2  | 2020-07-13 22:00:12.000 | 2020-07-14 07:10:28.000 | 6-8        |    1.166666 |
+----------+-------------------------+-------------------------+------------+-------------+

推荐阅读