Stack Overflow
Stack Overflow

Questions tagged [gaps-and-islands]

Gaps and islands problems involve finding a range of missing values (gaps) or a range of consecutive values (islands) in a sequence of numbers or dates.

Gaps and islands problems involve finding a range of missing values (gaps) or a range of consecutive values (islands) in a sequence of numbers or dates.

1372 questions
19
votes
5 answers

Group data by the change of grouping column value in order

With the following data create table #ph (product int, [date] date, price int) insert into #ph select 1, '20120101', 1 insert into #ph select 1, '20120102', 1 insert into #ph select 1, '20120103', 1 insert into #ph select 1, '20120104', 1 insert…
MrEdmundo
  • 5,105
  • 13
  • 46
  • 58
18
votes
4 answers

Check for x consecutive days - given timestamps in database

Could anybody give me an idea or hint how you could check for X consecutive days in a database table (MySQL) where logins (user id, timestamp) are stored? Stackoverflow does it (e.g. badges like Enthusiast - if you log in for 30 consecutive days or…
Chris
  • 3,756
  • 7
  • 35
  • 54
15
votes
3 answers

GROUP BY and aggregate sequential numeric values

Using PostgreSQL 9.0. Let's say I have a table containing the fields: company, profession and year. I want to return a result which contains unique companies and professions, but aggregates (into an array is fine) years based on numeric…
Joshua Burns
  • 8,268
  • 4
  • 48
  • 61
14
votes
3 answers

Find gaps of a sequence in SQL without creating additional tables

I have a table invoices with a field invoice_number. This is what happens when i execute select invoice_number from invoice: invoice_number -------------- 1 2 3 5 6 10 11 I want a SQL that gives me the following result: gap_start | gap_end 4 …
Mateus Viccari
  • 7,389
  • 14
  • 65
  • 101
14
votes
2 answers

SQL count consecutive days

This is the SQL database data: UserTable UserName | UserDate | UserCode ------------------------------------------- user1 | 08-31-2014 | 232 user1 | 09-01-2014 | 232 user1 | 09-02-2014 | 0 user1 | 09-03-2014…
majukivi
  • 145
  • 1
  • 1
  • 5
13
votes
1 answer

How to find the boundaries of groups of contiguous sequential numbers?

I have a table with the following definition CREATE TABLE mytable ( id INT IDENTITY(1, 1) PRIMARY KEY, number BIGINT, status INT ) and example data INSERT INTO mytable VALUES (100,0), (101,0), (102,0), …
Mike Station
  • 193
  • 1
  • 8
13
votes
5 answers

sql query to get deleted records

You have a table table1 that contains id column, that is int(11), not null, auto_increment and starts from 1. Suppose, you have 10,000 records. It is clear the id of the last record is 10,000. Once you removed 3 records, you have 9,997 records in…
Haradzieniec
  • 9,086
  • 31
  • 117
  • 212
12
votes
3 answers

Increment column for streaks

How do I get the following result highlighted in yellow? Essentially I want a calculated field which increments by 1 when VeganOption = 1 and is zero when VeganOption = 0 I have tried using the following query but using partition continues to…
user3904868
12
votes
8 answers

SQL interview question

I got following question on an interview: Given a table of natural numbers with some missing ones, provide output of two tables, beginning of number gap in first table and ending in second. Example: ____ ________ | | | | | | 1 | | 3…
Krns
  • 178
  • 1
  • 6
12
votes
1 answer

How to solve gaps and island problems in R and performance vs SQL?

I was wondering whether the island and gaps problems can be solved in R efficiently, similar to SQL. I have the following data available, if we examine one ID: ID StartDate StartTime EndDate EndTime 1 19-05-2014 19:00 19-05-2014 …
Snowflake
  • 2,869
  • 3
  • 22
  • 44
11
votes
3 answers

Group close numbers

I have a table with 2 columns of integers. The first column represents start index and the second column represents end index. START END 1 8 9 13 14 20 20 25 30 42 42 49 60 67 Simple So far. What I would like to do is group…
Liran Ben Yehuda
  • 1,568
  • 3
  • 12
  • 23
11
votes
2 answers

How can I identify groups of consecutive dates in SQL?

Im trying to write a function which identifies groups of dates, and measures the size of the group. I've been doing this procedurally in Python until now but I'd like to move it into SQL. for example, the list Bill 01/01/2011 Bill 02/01/2011 Bill…
Pythonn00b
  • 325
  • 1
  • 4
  • 20
10
votes
3 answers

SQL to find time elapsed from multiple overlapping intervals

Not using MSSQL or DB2 or Oracle. No CTE. No OVERLAP predicate. No INTERVAL data type. The situation: on a vehicle to be repaired work can not start until all parts ordered for the job have been received. Parts may be ordered multiple times…
jon
  • 111
  • 1
  • 5
10
votes
3 answers

Mark non-contiguous date ranges

Background (Input) The Global Historical Climatology Network has flagged invalid or erroneous data in its collection of weather measurements. After removing these elements, there are swaths of data that no longer have contiguously dated sections.…
Dave Jarvis
  • 30,436
  • 41
  • 178
  • 315
10
votes
2 answers

How to GROUP entries BY uninterrupted sequence?

CREATE TABLE entries ( id serial NOT NULL, title character varying, load_sequence integer ); and data INSERT INTO entries(title, load_sequence) VALUES ('A', 1); INSERT INTO entries(title, load_sequence) VALUES ('A', 2); INSERT INTO…
oskarae
  • 520
  • 2
  • 5
  • 16
1
2
3
…
91 92