Given a single column of effective dates, is there a SQL statement that can transform that into date ranges?
<p>Similar to another question I've posted, given the following table...</p>

<pre><code>Promo   EffectiveDate
------   -------------
PromoA  1/1/2016
PromoB  4/1/2016
PromoC  7/1/2016
PromoD  10/1/2016
PromoE  1/1/2017
</code></pre>

<p>What is the easiest way to transform it into start and end dates, like so...</p>

<pre><code>Promo   StartDate  EndDate
------  ---------  ---------
PromoA  1/1/2016   4/1/2016
PromoB  4/1/2016   7/1/2016
PromoC  7/1/2016   10/1/2016
PromoD  10/1/2016  1/1/2017
PromoE  1/1/2017   null (ongoing until a new Effective Date is added)
</code></pre>

<h2>Update</h2>

<p>Correlated queries seem to be the simplest solution, but as I understand it, they are extremely inefficient since the subquery has to run once per row of the outer select.</p>

<p>What I was thinking as a potential solution was something along the lines of selecting the values from the table a second time, but eliminating the first result, then pairing them up with the first select by ordinal index with a simple outer left join.</p>

<p>As an example, substituting letters for dates above, the first select would be like A,B,C,D,E and second would be B,C,D,E (which is the first select minus the first record 'A') then pairing them up by ordinal index with a simple outer left join, resulting in A-B, B-C, C-D, D-E, E-null.  However I couldn't figure out the syntax to make that work.</p>