title: How to make calculation on time intervals?
body: <p>I have a problem ,i solve it but i have written a long procedure and i can't be sure that it covers all the possible cases .</p>
  
  <p>The problem:</p>
  
  <p>If i have a <strong>main interval time</strong> (<code>From A to B</code>), and <strong>secondary interval times</strong> (Many or no)</p>
  
  <pre><code>(`From X to Y AND From X` to Y` AND X`` to  Y`` AND ....`) 
  </code></pre>
  
  <p>I want to <strong>SUM</strong> all parts of My Main interval time (AB) <strong>out of</strong> secondary intervals in <strong>minutes</strong> in efficient and the least number of conditions (SQL server Procedure and  C# method)?</p>
  
  <p>For Example : If my Main interval From <code>02:00  to  10:30</code>
  And say one secondary interval From <code>04:00 to 08:00</code></p>
  
  <p>Now i want this result : <code>((04:00 - 02:00) + (10:30 -08:00))* 60</code></p>
  
  <p>Example with graph :</p>
  
  <p>in the first case the result will be :</p>
  
  <pre><code>((X-A) + (B-Y)) * 60
  </code></pre>
  
  <p>and it will be more complicated when i have many secondary periods.</p>
  
  <h2>NOTE:</h2>
  
  <p>May be the overlap among the secondary intervals happening only when i have to compare the main period [A,B] to the <strong>UNION</strong> of <em>at most two parallel sets of secondary intervals</em> .the first set have to contain only one secondary interval and the the second set contains (many or no ) of secondary intervals .For example in the graph comparing <code>[A,B]</code> to (sets of <code>2,5</code>)the first set <code>(2)</code> consists of one secondary interval and the second set <code>(5)</code> consists of three secondary intervals . and this 's the most worst case ,i need to handle.</p>
  
  <p>For example :</p>
  
  <p>IF my main interval is <code>[15:00,19:40]</code>
  and i have two sets of secondary intervals .according to my rule at least one of these sets should consists of one secondary interval.
  say the first set is <code>[11:00 ,16:00]</code>
  and the second set is consists of say two secondary intervals <code>[10:00,15:00],[16:30,17:45]</code>
  Now i want the result <code>(16:30 -16:00) +(19:40 -17:45)</code></p>
  
  <hr>
  
  <p>According to the comments :</p>
  
  <p>My table is like this :</p>
  
  <p>The first table contains secondary periods ,at most two sets of secondary periods in the same date for specific employee. the first set contains only one secondary period in the work day <code>(W)</code> <code>[work_st,work_end]</code>,and this set  will be empty if the day is weekend <code>[E]</code> and in this case no overlap among the secondary periods. and the second set may contain many secondary periods in the same date <code>[check_in,check_out]</code> ,because the employee may check_in_out many times in the same day.</p>
  
  <pre><code>emp_num  day_date   work_st    work_end   check_in   check_out     day_state
  
    547    2015-4-1   08:00       16:00     07:45      12:10           W
    547    2015-4-1   08:00       16:00     12:45      17:24           W
    547    2015-4-2   00:00       00:00     07:11      13:11           E
  </code></pre>
  
  <p>The second table contains the main period<code>[A,B]</code> and it's a one period for this employee at that day (one record)</p>
  
  <pre><code>emp_num  day_date   mission_in    mission_out
    547    2015-4-1    15:00          21:30
    547    2015-4-2    8:00           14:00
  </code></pre>
  
  <hr>
  
  <p>In the previous example if i have a procedure or method as required this procedure should take two parameters :</p>
  
  <ul>
  <li>The Date</li>
  <li>The emp_num</li>
  </ul>
  
  <p>in the previous example it should be like this <code>('2015-4-1' ,547)</code></p>
  
  <p>According to my explanation :</p>
  
  <ul>
  <li><p>The Main period (Mission Period) <code>[A,B]</code> from the second table :
  Should be only one period in this date for that employee</p>
  
  <p><code>[15:00,21:30]</code></p></li>
  <li><p>The secondary period for the passed date <code>('2015-4-1')</code> for that employee was two
  sets of secondary periods (the worst case) from the first table </p>
  
  <p>The first set should contain only one secondary period (or zero
  periods) <code>[08:00,16:00]</code> the second set could contain many secondary
  periods (or zero periods) </p>
  
  <p><code>[07:45,12:10]</code>,<code>[12:45,17:24]</code></p></li>
  </ul>
  
  <blockquote>
    <p>The output should be [17:24,21:30] converted to minutes</p>
  </blockquote>
  
  <h2>Note</h2>
  
  <p>all <code>day_date,mission_in,mission_out,work_st,work_end,check_in,check_out</code>
  are <code>datetime</code> fields but i put just the time in the example for simplification , i want to ignore the date part except the <code>day_date</code> because it's the date which i calculate based on in addition to the <code>emp_num</code>.</p>
  
  <p><img src="https://i.stack.imgur.com/s1WwO.png" alt="enter image description here"></p>