How to get end date onto previous row

[Debbie-Leigh]

Hi,

I'm trying to extract start and end dates as the delimiters of different periods. I would like them to be on the same row, but I can't figure out how to do it.

The query I'm using is:
 

-- This select gets the start and end dates, but on different rows

    SELECT date AS start_date

         , date_sub(date, interval 1 second) AS end_date

      FROM log

     WHERE date(date) >= '2013-06-10 00:00:00'

       AND date(date)  < '2013-06-16 23:59:59'

       AND type in (1,2,3)

  GROUP BY date(date), type

     UNION

-- This next one gets the end date for the last date in the period

    SELECT date_sub(date, interval 1 second) AS end_date

         , date

      from (

             SELECT date

               FROM log

              WHERE date(date) > '2013-06-16 23:59:59'

                AND type IN (1,2,3)

           GROUP BY date(date), type

              LIMIT 1

           ) AS next_one

    ORDER BY start_date

An example of the data I'm working with is:
 

date                type

2013-06-06 13:05:44 1

2013-06-13 15:34:45 2

2013-06-13 15:31:08 1

2013-06-20 13:10:32 1

2013-06-27 14:05:16 1

2013-07-04 13:58:14 1

The result I'm currently getting is:
 

start_date          end_date

2013-06-13 15:31:08 2013-06-13 15:31:07

2013-06-13 15:34:45 2013-06-13 15:34:44

2013-06-20 13:10:31 2013-06-20 13:10:32

So now the end date for row 1 is in row 2 and the one for row 2 is in row 3 etc.

Does anyone know of a technique to get the end date in row 2 to appear in row 1 i.e. against the one it relates to?


Debbie
 

 

[Barand]

Does your table only contain the two columns (date, type) or is there also a key field that would group the dates together?