sql-server - SQL Server - 重叠数据的累积总和 - 获取总和达到给定值的日期

Question

在我们公司，我们的客户执行我们在不同表中记录的各种事件 - 面试出勤、类(class)出勤和其他一般事件。我有一个数据库 View ，它将所有这些表中的数据结合在一起，为我们提供了如下所示的 ActivityView。正如您所看到的，有些事件是重叠的 - 例如，在参加面试时，客户可能一直在执行简历更新事件。

+----------------------+---------------+---------------------+-------------------+
| activity_client_id   | activity_type | activity_start_date | activity_end_date |
+----------------------+---------------+---------------------+-------------------+
|                  112 | Interview     | 2015-06-01 09:00    | 2015-06-01 11:00  |
|                  112 | CV updating   | 2015-06-01 09:30    | 2015-06-01 11:30  |
|                  112 | Course        | 2015-06-02 09:00    | 2015-06-02 16:00  |
|                  112 | Interview     | 2015-06-03 09:00    | 2015-06-03 10:00  |
+----------------------+---------------+---------------------+-------------------+

每个客户都有一个“注册日期”，记录在客户表上，即他们加入我们计划的时间。这是我们的示例客户:

+-----------+---------------------+
| client_id | client_sign_up_date |
+-----------+---------------------+
|       112 | 2015-05-20          |
+-----------+---------------------+

我需要创建一个显示以下列的报告:

+-----------+---------------------+--------------------------------------------+
| client_id | client_sign_up_date | date_client_completed_5_hours_of_activity |
+-----------+---------------------+--------------------------------------------+

我们需要这份报告来了解我们的计划的有效性。该计划的一个重要目标是我们让每位客户尽快完成至少 5 小时的事件。因此，这份报告将告诉我们每个客户从注册开始需要多长时间才能达到这个数字。

更棘手的是，当我们计算 5 小时的总事件时，我们必须折扣重叠的事件:

在上面的示例数据中，客户在 09:00 到 11:00 之间参加了采访。
同日上午09:30至11:30还进行了简历更新事件。对于我们的计算，这将给他们一天 2.5 小时(150 分钟)的总事件 - 我们只会计算 30 分钟的简历更新，因为面试与该时间重叠直至 11:00。

因此我们的示例客户的报告将给出以下结果:

+-----------+---------------------+--------------------------------------------+
| client_id | client_sign_up_date | date_client_completed_5_hours_of_activity |
+-----------+---------------------+--------------------------------------------+
|       112 | 2015-05-20          | 2015-06-02                                 |
+-----------+---------------------+--------------------------------------------+

所以我的问题是如何使用 select 语句创建报告？我可以通过编写一个存储过程来弄清楚如何做到这一点，该存储过程将循环遍历 View 并将结果写入报告表。但我更愿意避免使用存储过程并使用一个 select 语句来即时提供报告。

我使用的是 SQL Server 2005。

Answer 1

请参阅 SQL Fiddle here 。

with tbl as (
  -- this will generate daily merged ovelaping time
  select distinct
    a.id
    ,(
        select min(x.starttime) 
        from act x 
        where x.id=a.id and ( x.starttime between a.starttime and a.endtime
          or a.starttime between x.starttime and x.endtime )
    ) start1
    ,(
        select max(x.endtime) 
        from act x 
        where x.id=a.id and ( x.endtime between a.starttime and a.endtime
          or a.endtime between x.starttime and x.endtime )
    ) end1
  from act a

), tbl2 as 
(
  -- this will add minute and total minute column
  select 
    * 
    ,datediff(mi,t.start1,t.end1) mi
    ,(select sum(datediff(mi,x.start1,x.end1)) from tbl x where x.id=t.id and x.end1<=t.end1) totalmi
  from tbl t
), tbl3 as 
(
  -- now final query showing starttime and endtime for 5 hours other wise null in case not completed 5(300 minutes) hours
  select 
    t.id
    ,min(t.start1) starttime
    ,min(case when t.totalmi>300 then t.end1 else null end) endtime
  from tbl2 t
  group by t.id
)
-- final result 
select *
from tbl3
where endtime is not null