MarqueIV MarqueIV - 1 year ago 62
SQL Question

Given a single column of effective dates, is there a SQL statement that can transform that into date ranges?

Similar to another question I've posted, given the following table...

Promo EffectiveDate
------ -------------
PromoA 1/1/2016
PromoB 4/1/2016
PromoC 7/1/2016
PromoD 10/1/2016
PromoE 1/1/2017

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

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)


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.

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.

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.

Answer Source

Yes, you can use a correlated query with LIMIT :

SELECT,t.effectiveDate as start_date,
       (SELECT s.effectiveDate FROM YourTable s
        WHERE >
        ORDER BY s.effectiveDate
        LIMIT 1) as end_date
FROM YourTable t

EDIT: Here is a solution with a join :

SELECT,t.effectiveDate as start_date,
       MIN(s.effectiveDate) as end_date
FROM YourTable t
LEFT JOIN YourTable s
 ON( <
GROUP BY,t.effectiveDate
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download