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

[NogDog]

How about something like:

SELECT type, MIN(date) AS start_date, MAX(date) AS end_date
FROM log
WHERE type IN(1,2,3) AND date BETWEEN '2014-01-01' AND 2014-02-01'
GROUP BY type ORDER BY type