Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jul 2007
    Thanked 0 Times in 0 Posts

    How to get end date onto previous row


    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
    -- 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?

    QuicknEasySalesPro.com - your quick and easy, yet powerful solution for managing your membership site sales, downloads and affiliates.

  2. #2
    Senior Coder
    Join Date
    Dec 2005
    Thanked 79 Times in 79 Posts
    One idea (not going into depth of logic of your dates) is to put your result (ordered by start date ?) into new table with autoinc field, then:

    select t1.start_date, t2.end_date from new_table t1
    join new_table t2 on t1.autoinc = t2.autoinc-1

    Didnt check that at all, just showing idea, join could be done on date field also I presume.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts