Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
01-25-2014, 08:26 PM #1
- 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 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
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
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
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?
DebbieQuicknEasySalesPro.com - your quick and easy, yet powerful solution for managing your membership site sales, downloads and affiliates.
02-01-2014, 06:04 PM #2
- 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.