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

在我们公司，我们的客户执行我们在不同表中记录的各种活动 - 面试出勤、课程出勤和其他一般活动。 我有一个数据库视图，它将所有这些表中的数据结合在一起，为我们提供了如下所示的 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 语句创建报告？ 我可以通过编写一个存储过程来弄清楚如何做到这一点，该存储过程将循环遍历视图并将结果写入报告表。 但我更愿意避免使用存储过程并使用一个 select 语句来即时提供报告。

我正在使用 SQL Server 2005。

请参阅 SQL Fiddlehere.

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