title: Selecting the first date after the MIN date
body: <p>This site has answered many a SQL questions for me. Finally signed up to ask one of my own and get active here.</p>
  
  <p>Anyways, I'm working in a table that has Date_Effective and Date_Lapse. A Client can have multiple rows so what I'm trying to get to is the number of days between a Date_Lapse and the next Date_Effective for the same client. The date values in this table are int's that I'll convert to dates later.</p>
  
  <p>The below code doesn't work. It doesn't like the second value I'm joining on. Why can't I get it to give me min date_effective that's greater than each date_effective? If I run the below I just get no results because it's seeing it as there are no effective dates greater than the min effective date.</p>
  
  <pre><code>SELECT ClientID, c1.Date_Lapse, c2.Date_Effective
  FROM Fact_Episodes c1
  LEFT JOIN (     
              SELECT ClientID, min(Date_Effective) as Date_Effective
              FROM Fact_Episodes 
              GROUP BY ClientID
            ) c2
  ON c1.ClientID = c2.ClientID
  AND c2.Date_Effective &gt; c1.Date_Effective
  </code></pre>