title: SQL Server - cumulative sum on overlapping data - getting date that sum reaches a given value
body: <p>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.</p>
  
  <pre><code>+----------------------+---------------+---------------------+-------------------+
  | 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  |
  +----------------------+---------------+---------------------+-------------------+
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>+-----------+---------------------+
  | client_id | client_sign_up_date |
  +-----------+---------------------+
  |       112 | 2015-05-20          |
  +-----------+---------------------+
  </code></pre>
  
  <p>I need to create a report that will show the following columns:</p>
  
  <pre><code>+-----------+---------------------+--------------------------------------------+
  | client_id | client_sign_up_date | date_client_completed_5_hours_of_activity |
  +-----------+---------------------+--------------------------------------------+
  </code></pre>
  
  <p>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.</p>
  
  <p>What makes this even trickier is that when we calculate 5 hours of total activity, we must discount overlapping activities:</p>
  
  <p>In the sample data above the client attended an interview between 09:00 and 11:00.<br>
  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.</p>
  
  <p>So the report for our sample client would give the following result:</p>
  
  <pre><code>+-----------+---------------------+--------------------------------------------+
  | client_id | client_sign_up_date | date_client_completed_5_hours_of_activity |
  +-----------+---------------------+--------------------------------------------+
  |       112 | 2015-05-20          | 2015-06-02                                 |
  +-----------+---------------------+--------------------------------------------+
  </code></pre>
  
  <p>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.</p>
  
  <p>I am using SQL Server 2005. </p>