Counting intersecting time intervals in T-SQL
<p>CODE:</p>

<pre><code>CREATE TABLE #Temp1 (CoachID INT, BusyST DATETIME, BusyET DATETIME)
CREATE TABLE #Temp2 (CoachID INT, AvailableST DATETIME, AvailableET DATETIME)

INSERT INTO #Temp1 (CoachID, BusyST, BusyET)
SELECT 1,'2016-08-17 09:12:00','2016-08-17 10:11:00'
UNION
SELECT 3,'2016-08-17 09:30:00','2016-08-17 10:00:00'
UNION
SELECT 4,'2016-08-17 12:07:00','2016-08-17 13:10:00'

INSERT INTO #Temp2 (CoachID, AvailableST, AvailableET)
SELECT 1,'2016-08-17 09:07:00','2016-08-17 11:09:00'
UNION
SELECT 2,'2016-08-17 09:11:00','2016-08-17 09:30:00'
UNION
SELECT 3,'2016-08-17 09:24:00','2016-08-17 13:08:00'
UNION
SELECT 1,'2016-08-17 11:34:00','2016-08-17 12:27:00'
UNION
SELECT 4,'2016-08-17 09:34:00','2016-08-17 13:00:00'
UNION
SELECT 5,'2016-08-17 09:10:00','2016-08-17 09:55:00'

--RESULT-SET QUERY GOES HERE

DROP TABLE #Temp1
DROP TABLE #Temp2
</code></pre>

<p>DESIRED OUTPUT:</p>

<pre><code>CoachID CanCoachST                  CanCoachET                  NumOfCoaches
1       2016-08-17 09:12:00.000     2016-08-17 09:24:00.000     2 --(ID2 = 2,5)
1       2016-08-17 09:24:00.000     2016-08-17 09:30:00.000     3 --(ID2 = 2,3,5)
1       2016-08-17 09:30:00.000     2016-08-17 09:34:00.000     1 --(ID2 = 5)
1       2016-08-17 09:34:00.000     2016-08-17 09:55:00.000     2 --(ID2 = 4,5)
1       2016-08-17 09:55:00.000     2016-08-17 10:00:00.000     1 --(ID2 = 4)
1       2016-08-17 10:00:00.000     2016-08-17 10:11:00.000     2 --(ID2 = 3,4)
3       2016-08-17 09:30:00.000     2016-08-17 09:34:00.000     1 --(ID2 = 5)
3       2016-08-17 09:34:00.000     2016-08-17 09:55:00.000     2 --(ID2 = 4,5)
3       2016-08-17 09:55:00.000     2016-08-17 10:00:00.000     1 --(ID2 = 4)
4       2016-08-17 12:07:00.000     2016-08-17 12:27:00.000     2 --(ID2 = 1,3)
4       2016-08-17 12:27:00.000     2016-08-17 13:08:00.000     1 --(ID2 = 3)
4       2016-08-17 13:08:00.000     2016-08-17 13:10:00.000     0 --(No one is available)
</code></pre>

<p>GOAL:
Consider <code>#Temp1</code> as the table of team coaches (ID1) and their meeting times (ST1 = Meeting Start Time and ET1 = Meeting End Time).
Consider <code>#Temp2</code> as the table of team coaches (ID2) and their total available times (ST2 = Available Start Time and ET2 = Available End Time).</p>

<p>Now, the goal is to find all possible coaches from <code>#Temp2</code> who are available to coach during the meeting time of the coaches from <code>#Temp1</code>.</p>

<p>So for example, For the coach ID1 = 1, who is busy between 9:12 and 10:11 (data can span across multiple days, if that info matters), we have 
coach ID2 = 2 and 5 that can coach between 9:12 and 9:24
, coach ID2 = 2,3, and 5 that can coach between 9:24 and 9:30
, coach ID2 = 5 that can coach between 9:30 and 9:34
, coach ID2 = 4 and 5 that can coach between 9:34 and 9:55
, coach ID2 = 4 that can coach between 9:55 and 10:00
, and coach ID2 = 3 and 4 that can coach between 10:00 and 10:11 (note how ID 3, although available in #Temp2 table between 9:24 and 13:08, it can't coach for ID1 = 1 between 9:24 and 10:00 because its also busy between 9:30 and 10:00.</p>

<p>My effort so far: Only dealing with breaking #Temp1's time bracket so far. Still need to figure out A) how to remove that non-busy time window from the output B) add a field/map it to right T1's CoachIDs.</p>

<pre><code>;WITH ED
AS (SELECT BusyET, CoachID FROM #Temp1  
    UNION ALL   
    SELECT BusyST, CoachID FROM #Temp1
    )
,Brackets
AS (SELECT MIN(BusyST) AS BusyST
        ,(  SELECT MIN(BusyET)
            FROM ED e
            WHERE e.BusyET &gt; MIN(BusyST)
            ) AS BusyET
    FROM #Temp1 T   
    UNION ALL   
    SELECT B.BusyET
        ,e.BusyET
    FROM Brackets B
    INNER JOIN ED E ON B.BusyET &lt; E.BusyET
    WHERE NOT EXISTS (
            SELECT *
            FROM ED E2
            WHERE E2.BusyET &gt; B.BusyET
                AND E2.BusyET &lt; E.BusyET
            )
    )
SELECT *
FROM Brackets
ORDER BY BusyST;
</code></pre>

<p>I think I need to join on comparing ST/ET dates between two tables where IDs don't match each other. But I'm having trouble figuring out how to actually get only the meeting time window and unique count.</p>

<p>Updated with better schema/data-set. Also note, even though CoachID 4 is not "scheduled" to be available, he's still listed as busy for that last few minutes. And there can be scenario where no one else is available to work during that time, in which case, we can return 0 cnt record (or not return it if it's really complicated).</p>

<p>Again, the goal is to find count and combination of all available CoachIDs and their Available time window that can coach for the CoachIDs listed in the busy table.</p>

<p>Updated with more sample description matching sample data.</p>