MySQL Forums
Forum List  »  Views

Re: grouping date ranges
Posted by: Richard Boaz
Date: October 22, 2010 07:38AM

all best understood by example:

the output produced by your suggested SQL results in:

+---------+------------+------------+
| sampInt | min(start) | max(end) |
+---------+------------+------------+
| 4000 | 2006-08-03 | 2006-08-13 |
| 8333 | 2006-08-10 | 2006-08-18 |
+---------+------------+------------+

but as can be seen from my example desired output, that's not what i want. i want the date boundaries for each sample rate *change*, so that i know when each change starts and ends. the suggested query simply groups the sample rates by start and end dates, thus losing the boundaries where the changes actually occur.

the output i need is:

+-------------+-------------+------------+
| sampInt | date(start) | date(end) |
+-------------+-------------+------------+
| 4000 | 2006-08-03 | 2006-08-06 |
| 8333 | 2006-08-10 | 2006-08-12 |
| 4000 | 2006-08-13 | 2006-08-13 |
| 8333 | 2006-08-16 | 2006-08-18 |
+-------------+-------------+------------+

in real words:
this output shows you that the sample rate was 4000 between 03-aug and 06-aug (inclusive), changed to 8333 for the period 10-aug to 12-aug, changed back to 4000 for 13-aug, and then changed back again to 8333 for the period 16-aug to 18-aug.

playing around, this is the SQL i have come up with so far, (but i'm unhappy with it since it is inefficient and takes way too long on a table with 2 million rows):

SELECT
t1.chni_fk,
t1.sampint,
t1.start,
coalesce(( SELECT t4.end
FROM TRC t4
WHERE t4.chni_fk = t1.chni_fk
AND (t4.end <
(SELECT t2.end
FROM TRC t2
WHERE t1.chni_fk = t2.chni_fk
AND (t2.sampInt <> t1.sampInt)
AND t2.end > t1.start
ORDER BY t2.end ASC LIMIT 1))
ORDER BY t4.end DESC LIMIT 1),
(SELECT max(end) FROM TRC where chni_fk = t1.chni_fk)) as end
FROM TRC t1
WHERE
t1.sampint <> (SELECT t3.sampInt
FROM TRC t3
WHERE t3.chni_fk = t1.chni_fk and date(t3.end) <= date(t1.start)
ORDER BY t3.end DESC LIMIT 1,1)
ORDER BY 1, 3;

Options: ReplyQuote


Subject
Views
Written By
Posted
3517
October 21, 2010 02:55AM
1541
October 21, 2010 07:35AM
1446
October 21, 2010 07:47AM
1529
October 22, 2010 07:22AM
Re: grouping date ranges
1638
October 22, 2010 07:38AM
1472
October 22, 2010 08:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.