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

在我们公司，我们的客户会执行我们在不同表中登录的各种活动 - 面试出勤、课程出勤和其他一般活动.我有一个数据库视图，它将所有这些表中的数据联合起来，为我们提供了如下所示的 ActivityView.正如您所看到的，有些活动是重叠的 - 例如，在参加面试时，客户可能一直在执行简历更新活动.

In our company, our clients perform various activities that we log in different tables - Interview attendance, Course Attendance, and other general activities. I have a database view that unions data from all of these tables giving us the ActivityView that looks like this. As you can see some activities overlap - for example while attending an interview, a client may have been performing a CV update activity.

+----------------------+---------------+---------------------+-------------------+
| 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  |
+----------------------+---------------+---------------------+-------------------+

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

Each client has a "Sign Up Date", recorded on the client table, which is when they joined our programme. Here it is for our sample client:

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

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

I need to create a report that will show the following columns:

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

我们需要这份报告来了解我们的计划有多有效.该计划的一个重要目标是我们让每位客户尽快完成至少 5 小时的活动.所以这份报告会告诉我们每个客户从注册到达到这个数字需要多长时间.

We need this report in order to see how effective our programme is. An important aim of the programme is that we get every client to complete at least 5 hours of activity as quickly as possible. So this report will tell us how long from sign up does it take each client to achieve this figure.

更棘手的是，当我们计算 5 小时的总活动时，我们必须扣除重叠的活动:

What makes this even trickier is that when we calculate 5 hours of total activity, we must discount overlapping activities:

在上面的示例数据中，客户在 09:00 到 11:00 之间参加了一次采访.
同一天，他们还从09:30到11:30进行了简历更新活动.对于我们的计算，这将使他们一天的总活动时间为 2.5 小时(150 分钟)——我们只计算 30 分钟的简历更新，因为面试将其重叠到 11:00.

In the sample data above the client attended an interview between 09:00 and 11:00.
On the same day they also performed CV updating activity from 09:30 to 11:30. For our calculation, this would give them total activity for the day of 2.5 hours (150 minutes) - we would only count 30 minutes of the CV updating as the Interview overlaps it up to 11:00.

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

So the report for our sample client would give the following result:

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

所以我的问题是如何使用 select 语句创建报告?我可以通过编写一个存储过程来解决这个问题，该过程将遍历视图并将结果写入报告表.但我更愿意避免使用存储过程，并使用一个 select 语句来即时提供报告.

So my question is how can I create the report using a select statement ? I can work out how to do this by writing a stored procedure that will loop through the view and write the result to a report table. But I would much prefer to avoid a stored procedure and have a select statement that will give me the report on the fly.

我使用的是 SQL Server 2005.

I am using SQL Server 2005.