This is a second answer, I’ll post it separately. SELECT DATE(r1.reportdate) + INTERVAL 1 DAY AS missing_date FROM Reports r1 LEFT OUTER JOIN Reports r2 ON DATE(r1.reportdate) = DATE(r2.reportdate) – INTERVAL 1 DAY WHERE r1.reportdate BETWEEN ‘2011-01-01’ AND ‘2011-04-30’ AND r2.reportdate IS NULL; This is a self-join that reports a date such that no row … Read more
SELECT done, count(*) FILTER (WHERE step) OVER (ORDER BY done) AS grp FROM ( SELECT done , lag(done) OVER (ORDER BY done) <= done – interval ‘2 min’ AS step FROM tbl ) sub ORDER BY done; The subquery sub returns step = true if the previous row is at least 2 min away – … Read more
You are going to somehow need a table of days and hours, and then you will have to do an outer join between that table and your query. Here’s how I would do it. Note that this solution will only work in SQL Server 2005 and 2008. If you don’t have those platforms, you’ll have … Read more
As mentioned in the comments this is a classic gaps and islands problem. A solution popularized by Itzik Ben Gan is to use the fact that ROW_NUMBER() OVER (ORDER BY number) – number remains constant within an “island” and cannot appear in multiple islands. WITH T AS (SELECT ROW_NUMBER() OVER (ORDER BY number) – number … Read more
This is a Gaps and Islands problem. The easiest way to solve this is using ROW_NUMBER() to identify the gaps in the sequence: SELECT UserName, UserDate, UserCode, GroupingSet = DATEADD(DAY, -ROW_NUMBER() OVER(PARTITION BY UserName ORDER BY UserDate), UserDate) FROM UserTable; This gives: UserName | UserDate | UserCode | GroupingSet ————+—————+————+————- user1 | 09-01-2014 | 1 … Read more
This is known as the “islands” problem. Using Itzik Ben Gan’s approach: ;WITH YourTable AS ( SELECT 1 AS N, ‘A’ AS C UNION ALL SELECT 2 AS N, ‘A’ AS C UNION ALL SELECT 3 AS N, ‘A’ AS C UNION ALL SELECT 4 AS N, ‘B’ AS C UNION ALL SELECT 5 AS … 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
You can accomplish this using a shifted self-outer-join in conjunction with a variable. See this solution: SELECT IF(COUNT(1) > 0, 1, 0) AS has_consec FROM ( SELECT * FROM ( SELECT IF(b.login_date IS NULL, @val:=@val+1, @val) AS consec_set FROM tbl a CROSS JOIN (SELECT @val:=0) var_init LEFT JOIN tbl b ON a.user_id = b.user_id AND … Read more
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
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