title: Finding start and end date for events in one table
body: <p>I have a single table where events are stored with start and end dates in different rows. Now I want to query all corresponding pairs of Start and End dates to create a report.</p>
  
  <pre><code>CREATE TABLE EVENTS (
   Id INT IDENTITY(1,1) PRIMARY KEY,
   DateEntry datetime,
   IsStart bit,
   EventType varchar(12)
  )
  
  INSERT INTO EVENTS
  VALUES
  ('2016-06-16', 1, 'EVENT A'),
  ('2016-06-16', 1, 'EVENT B'),
  ('2016-06-17', 0, 'EVENT A'),
  ('2016-06-18', 1, 'EVENT A'),
  ('2016-06-19', 0, 'EVENT A'),
  ('2016-06-20', 0, 'EVENT B'),
  ('2016-06-21', 1, 'EVENT C')
  
  
  SELECT EventType, DateEntry as StartDate, EndDate = LEAD(DateEntry, 1) OVER ( PARTITION BY EventType ORDER BY DateEntry)
  FROM Events
  ORDER BY EventType, DateEntry
  </code></pre>
  
  <p>This query returns:</p>
  
  <pre><code>| EventType | StartDate  | EndDate    |
  =======================================
  | EVENT A   | 2016-06-16 | 2016-06-17 |
  | EVENT A   | 2016-06-17 | 2016-06-18 |
  | EVENT A   | 2016-06-18 | 2016-06-19 |
  | EVENT A   | 2016-06-19 | NULL       |
  | EVENT B   | 2016-06-16 | 2016-06-20 |
  | EVENT B   | 2016-06-20 | NULL       | 
  | EVENT C   | 2016-06-21 | NULL       | 
  </code></pre>
  
  <p>I am expecting this result:</p>
  
  <pre><code>| EventType | StartDate  | EndDate    |
  =======================================
  | EVENT A   | 2016-06-16 | 2016-06-17 |
  | EVENT A   | 2016-06-18 | 2016-06-19 |
  | EVENT B   | 2016-06-16 | 2016-06-20 |
  | EVENT C   | 2016-06-21 | NULL       |
  </code></pre>
  
  <p>I need to add a filter to only look for start dates and apply the LEAD function on it, but if I add a WHERE-clause it will completely ignore all EndDates.</p>
  
  <p>With an inline query I am able to get the right result, but I'm not sure if this would be the best solution</p>
  
  <pre><code>SELECT * FROM (
  SELECT EventType, IsStart, DateEntry as StartDate, EndDate = LAG(DateEntry, 1) OVER ( PARTITION BY EventType ORDER BY DateEntry DESC)
  FROM Events) a
  WHERE IsStart = 1
  </code></pre>