Re: Group by Consecutive Dates

[Peter Brawley]

If I understand you correctly, it's the "Find sequence starts and ends" query pattern (http://www.artfulsoftware.com/queries.php), extended to group by user:

SELECT a.user, a.d AS Start, MIN( c.d ) AS End 
FROM t      AS a
LEFT JOIN t AS b ON a.user=b.user AND a.d = AddDate(b.d,1)
LEFT JOIN t AS c ON a.user=c.user AND a.d <= c.d
LEFT JOIN t AS d ON c.user=d.user AND c.d = AddDate(d.d,-1)
WHERE b.d IS NULL AND c.d IS NOT NULL AND d.d IS NULL
GROUP BY a.user,a.d;

PB
http://www.artfulsoftware.com