title: T-SQL Query to Identify Date Ranges when an Event Happens
body: <p>I am trying to identify the date ranges when an organization is on our "monitor" list.</p>
  
  <p>My data looks like this:</p>
  
  <pre><code>OrgCode OrgName           ReviewDate    MonitorList
  8000    Organization A    3/6/2014      1
  8000    Organization A    6/4/2014      1
  8000    Organization A    9/4/2014      1
  8000    Organization A    12/4/2014     0
  8000    Organization A    3/5/2015      1
  8000    Organization A    6/4/2015      1
  8000    Organization A    9/16/2015     1
  8000    Organization A    12/16/2015    1
  8000    Organization A    3/9/2016      1
  8000    Organization A    6/2/2016      1
  8000    Organization A    9/8/2016      1
  8000    Organization A    12/8/2016     1
  8000    Organization A    3/9/2017      0
  8000    Organization A    6/14/2018     0
  </code></pre>
  
  <p>The query output I'm seeking looks like this:</p>
  
  <pre><code>OrgCode OrgName           MonitorStartDate  MonitorEndDate
  8000    Organization A    3/6/2014          12/4/2014
  8000    Organization A    3/5/2015          3/9/2017
  </code></pre>
  
  <p>This organization, Organization A, has appeared on our monitor list twice: from 3/6/2014 to 12/4/2014, and 3/5/2015 to 3/9/2017.</p>
  
  <p>I've tried to accomplish this in a few ways, including,</p>
  
  <ul>
  <li>Varieties of <code>LEAD()</code> and <code>LAG()</code>; and,</li>
  <li><code>GROUP BY OrgCode, OrgName, MonitorList</code> and defining MonitorStartDate as <code>MIN(ReviewDate)</code> and MonitorEndDate as <code>MAX(ReviewDate)</code>.</li>
  </ul>
  
  <p>The second method did not account for the fact that these organizations may be on/off the monitor list multiple times. I still think some combinations of <code>LEAD()</code> or <code>LAG()</code> might work; but, not by themselves.</p>
  
  <p>Any guidance you folks can provide would be great and thanks for the help!</p>