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
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
Identifying non-consecutive values is always a bit tricky and involves several nested sub-queries (at least I cannot come up with a better solution). The first step is to identify non-consecutive values for the year: Step 1) Identify non-consecutive values select company, profession, year, case when row_number() over (partition by company, profession order by year) = … Read more
Update ConfexianMJS provided much better answer in terms of performance. The (not as fast as possible) answer Here’s version that works on 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 arrc_vouchers t1 WHERE NOT EXISTS … 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
I hope you will figure out the rest. select * from ( select date_add(‘2003-01-01 00:00:00.000’, INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from (select 0 as num union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union … 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
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