WITH q AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY crew, dayType ORDER BY [date]) AS rnd, ROW_NUMBER() OVER (PARTITION BY crew ORDER BY [date]) AS rn FROM mytable ) SELECT MIN([date]), MAX([date]), crew AS name, dayType FROM q GROUP BY crew, dayType, rnd – rn This article may be of interest to you: Things … Read more
No joins or recursive CTEs needed. The standard gaps-and-island solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group. WITH t AS ( SELECT InfoDate d,ROW_NUMBER() OVER(ORDER BY InfoDate) i FROM @d GROUP BY InfoDate … Read more
Find the first row where there does not exist a row with Id + 1 SELECT TOP 1 t1.Id+1 FROM table t1 WHERE NOT EXISTS(SELECT * FROM table t2 WHERE t2.Id = t1.Id + 1) ORDER BY t1.Id Edit: To handle the special case where the lowest existing id is not 1, here is a … Read more
How about something like: select (select isnull(max(val)+1,1) from mydata where val < md.val) as [from], md.val – 1 as [to] from mydata md where md.val != 1 and not exists ( select 1 from mydata md2 where md2.val = md.val – 1) giving summarised results: from to ———– ———– 6 6 8 8 11 14
MySQL doesn’t have recursive functionality, so you’re left with using the NUMBERS table trick – Create a table that only holds incrementing numbers – easy to do using an auto_increment: DROP TABLE IF EXISTS `example`.`numbers`; CREATE TABLE `example`.`numbers` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Populate the table … Read more
I would use this stored procedure to generate the intervals you need into the temp table named time_intervals, then JOIN and aggregate your data table with the temp time_intervals table. The procedure can generate intervals of all the different types you see specified in it: call make_intervals(‘2009-01-01 00:00:00′,’2009-01-10 00:00:00′,1,’DAY’) . select * from time_intervals . … Read more
How about (and please make sure the previous statement ended with a semi-colon): WITH numberedrows AS (SELECT ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY CreationDate) – DATEDIFF(day,’19000101′,CreationDate) AS TheOffset, CreationDate, UserID FROM tablename) SELECT MIN(CreationDate), MAX(CreationDate), COUNT(*) AS NumConsecutiveDays, UserID FROM numberedrows GROUP BY UserID, TheOffset The idea being that if we have list of … Read more
In MySQL and PostgreSQL: SELECT id + 1 FROM mytable mo WHERE NOT EXISTS ( SELECT NULL FROM mytable mi WHERE mi.id = mo.id + 1 ) ORDER BY id LIMIT 1 In SQL Server: SELECT TOP 1 id + 1 FROM mytable mo WHERE NOT EXISTS ( SELECT NULL FROM mytable mi WHERE mi.id … Read more
A better answer ConfexianMJS provided a much better answer in terms of performance. The (not as fast as possible) answer Here’s a version that works on a table of any size (not just on 100 rows): SELECT (t1.id + 1) as gap_starts_at, (SELECT MIN(t3.id) -1 FROM arrc_vouchers t3 WHERE t3.id > t1.id) as gap_ends_at FROM … Read more