title: SQL Joining / merging two event tables, sensitive to corresponding time
body: <p>Table 1 has open times.</p>
  <pre><code>id |  OpenTime
  1  | 2019-12-02 16:52:42.9130000
  1  | 2019-12-02 16:55:57.5560000
  1  | NULL
  1  | 2019-12-02 16:59:09.5640000
  1  | 2019-12-02 17:01:35.3510000
  2  | 2019-12-02 17:02:55.0270000
  2  | 2019-12-02 17:05:41.3930000
  2  | 2019-12-02 17:07:41.7870000
  </code></pre>
  <p>Table 2 has close times.</p>
  <pre><code>id |  CloseTime
  1  | NULL
  1  | 2019-12-02 16:56:19.2560000
  1  | 2019-12-02 16:57:47.5790000
  1  | 2019-12-02 16:59:33.5390000
  1  | 2019-12-02 17:01:55.6040000
  2  | 2019-12-02 17:04:00.7780000
  2  | 2019-12-02 17:06:04.4830000
  </code></pre>
  <p>I need to do a <code>DATEDIFF</code> calculation for each open to close time.</p>
  <p>It can only be open once at a time, however, we can be missing that event.</p>
  <p>We may not yet have the close event.</p>
  <p>An OpenTime will normally have a corresponding CloseTime, with CloseTime being greater than OpenTime, but less than the next OpenTime.</p>
  <pre><code>id |  OpenTime                    | CloseTime                    | Datedif
  1  | 2019-12-02 16:52:42.9130000  | NULL                         | NULL
  1  | 2019-12-02 16:55:57.5560000  | 2019-12-02 16:56:19.2560000  | 
  1  | NULL                         | 2019-12-02 16:57:47.5790000  | NULL
  1  | 2019-12-02 16:59:09.5640000  | 2019-12-02 16:59:33.5390000  | 
  1  | 2019-12-02 17:01:35.3510000  | 2019-12-02 17:01:55.6040000  | 
  2  | 2019-12-02 17:02:55.0270000  | 2019-12-02 17:04:00.7780000  | 
  2  | 2019-12-02 17:05:41.3930000  | 2019-12-02 17:06:04.4830000  | 
  2  | 2019-12-02 17:07:41.7870000  | NULL                         | NULL
  </code></pre>
  <p>Datediff will just be in seconds <code>DATEDIFF(SECOND,OpenTime,CloseTime)</code>.</p>
  <pre><code>DROP TABLE IF EXISTS #NEWTABLE;
  SELECT 
    a.ID,
    MAX(a.OpenTime) as OpenTime,
    MIN(b.CloseTime) as CloseTime ,
    DATEDIFF(SECOND, a.OpenTime, b.CloseTime) AS diffSeconds
  INTO #NEWTABLE
  FROM Table1  a
  JOIN Table2 b
    ON a.Id= b.Id
  WHERE 
  a.Id = b.ID
  and b.CloseTime&gt; = a.OpenTime
  
  Group by a.Id,DATEDIFF(SECOND, a.OpenTime, b.CloseTime)
  order by diffSeconds desc
  </code></pre>
  <p>This is not working, but I've tried to make sure that I get the correct corresponding CloseTime to each OpenTime. My code gives 26 rows output for 8 Open and Close events.</p>