# 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.

157 questions
Filter by
Sorted by
Tagged with
33k views

### Find "n" consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
• 767
4k views

### Group daily schedule into [Start date; End date] intervals with the list of week days

I need to convert data between two systems. First system stores schedules as a plain list of dates. Each date that is included in the schedule is one row. There can be various gaps in the sequence ...
• 4,577
4k views

### Form groups of consecutive rows with same value

I have a situation I think can be solved using window function but I'm not sure. Imagine the following table CREATE TABLE tmp ( date timestamp , id_type integer ) ; INSERT INTO tmp (date, id_type) ...
• 163
10k views

### Select longest continuous sequence

I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specific column. Consider the following table: lap_id (serial), lap_no (int), car_type (...
• 341
2k views

### Calculate Total Visits

I am trying to write a query where I have to calculate number of visits for a customer by taking care of overlapping days. Suppose for itemID 2009 start date is 23rd and end date is 26th therefore ...
• 3,993
3k views

### Populating missing data based on previous month-end values

Given the following data: create table #histories ( username varchar(10), account varchar(10), assigned date ); insert into #histories values ('PHIL','ACCOUNT1','2017-01-04'), ('...
• 31.4k
14k views

### How do I merge similar records with different validity dates?

The table I am working on has three components: An ID column (primary key in another table) Some data columns Date valid from/to columns. Values: ID Data From To 1 a 2015-01-01 ...
• 255
2k views

### Gaps and islands: client solution vs T-SQL query

Can a T-SQL solution for gaps and islands run faster than a C# solution running on the client? To be specific, let us provide some test data: CREATE TABLE dbo.Numbers ( n INT NOT NULL ...
• 7,154
3k views

### Compacting a sequence in PostgreSQL

I have a id serial PRIMARY KEY column in a PostgreSQL table. Many ids are missing because I have deleted the corresponding row. Now I want to "compact" the table by restarting the sequence and ...
• 515
9k views

### Selecting "consecutive" rows with same values

I have a table with the following data: userID tStamp status ------ ------------------- ------ Jason 2017-10-18 03:20:00 idle Brown 2017-10-18 03:20:28 idle Brown ...
• 103
11k views

### Using Row_Number to find consecutive row count

I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row If my data looks like this 724 727 728 733 735 737 743 747 ...
10k views

### Solving "Gaps and Islands" with row_number() and dense_rank()?

How does one solve the islands part of gaps-and-islands with dense_rank() and row_number(). I've seen this now a few times and I'm wondering if someone could explain it, Let's use something like this ...
• 59.6k
8k views

### Can I calculate ROW_NUMBER() for only consecutive records?

I need to calculate sequence numbers for consecutive values. That sounds like a job for ROW_NUMBER()! DECLARE @Data TABLE ( Sequence TINYINT NOT NULL PRIMARY KEY, Subset CHAR(1) NOT ...
• 5,887
5k views

### Select rows starting from specified value until another specified value

I have a table containing several columns and want to check for a column value, select the rows after that column value until a separate column value shows up. I have tried using BETWEEN, but if the ...
• 183
4k views

### Carry over long sequence of missing values with Postgres

I have a table like this: create table foo (foo_label text, foo_price int, foo_date date); insert into foo ( values ('aaa', 100, '2017-01-01'), ('aaa', NULL, '2017-02-...
• 1,153
7k views

### Finding missing gaps of data in a table with ~2.5 Million rows

I work for an economics consulting company and all of our data is housed in SQL Server 11 (2012). Our data essentially consists of a date column, a raw data column, several calculations columns and a ...
• 63
2k views

### Finding contiguous ranges in grouped data

I have a table with the following structure: CREATE TABLE `Rings` ( ID_RingType CHAR(2), Number MEDIUMINT UNSIGNED, ID_User INT(11) ); and with data: INSERT INTO `Rings` VALUES ('AA',...
• 324
673 views

### Group data by non-unique keys by distinct time range

I have data in an Oracle table like the following: PROJ_NBR STATUS START_DT AL20 AC 1/14/2010 4:31 AL20 AC 1/14/2010 4:32 AL20 AC ...
• 269
2k views

### Updating rows conditionally with a gapless ascending value

A user has a table they would like to update. Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less. The rows ...
• 23.7k
1k views

### Performance tuning for gap analysis of table

I have a table which stores the sequence (counter) of data received from devices out in the field. At any rate, these sequences need to be in order within a configurable time span, but can come into ...
326 views

### Selecting 'edge' records in a sequence of data

I have a table of items which run sequentially by date with both a start and end for each entry: ID | Start | End ---------------------------------------- 1 | 2012-08-20 00:00 | 2012-08-...
• 317
8k views

### Check if contiguous date interval exists

I have a table in PostgreSQL which describes some events that have a start date and an end date: CREATE TABLE my_table ( event_id serial NOT NULL, start_date timestamp without time zone NOT NULL,...
• 829
595 views

### Show current row "win streak"

I need to show the running win/loss streak per row in a query, so given the table below, the query should return the "expected" column. I've tried some approaches with window functions, but no success....
24k views

### How to get minimum and maximum for grouped timestamps

I would love to know how to create a view that groups timestamps in 10 minute nearest 10 minute intervals and contains each minimum and maximum timestamp for each. So a table that looks like this: | ...
934 views

### GROUP BY possible sequences

I have a table containing the following data, using Postgres 9.6: log_id | sequence | made_at (timestamp) 206480 1 1 206480 1 2 206480 2 3 206480 3 ...
• 195
616 views

### Query to get queue position for each group

I have a queue table with the following sample data: id company location 1 acme new york 2 acme philadelphia 3 genco st.louis 4 genco san diego 5 genco san francisco 6 acme ...
• 117
2k views

### Running out of disk space running MySQL query

I have been trying to create a "gaps and islands" identification routine in MySQL. See the previous question regarding setup here and computational resources required here. I increased the RAM of the ...
• 225
5k views

### Grouping data based on cumulative sum

I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb. I am using ...
• 63
5k views

### How to group data within a time span

I have a group of rows sorted by date as below: 2017-01-01 2017-01-01 2017-01-02 2017-01-03 2017-01-04 2017-01-17 2017-01-18 2017-01-18 2017-01-18 2017-01-19 2017-01-19 2017-01-26 2017-01-27 2017-01-...
937 views

### Gaps and Islands - Finding the Nearest Island

I am working with the following scenario where I have temporal data that falls into islands and gaps. Every once in a while I need to associate an event that falls within an existing gap to its ...
• 9,202
262 views

### Recursive Join to get Spans

I am pretty new to working in SQL at and have a gaps and islands problem in which I am trying to get a series of consecutive months of membership. Any member may have multiple memberships and their ...
659 views

### find the beginning and ending values of sequential runs

This article does the inverse of what I need: Find which numbers in [1, 161] are not in the result set? Given the sample data they used: mysql> CREATE TABLE wp_blogs -> ( -> ...
• 131
839 views

### identify gaps and islands

I have a database that has the following structure +---------------------+------------+---------------+ | timestamp | EngOilP_sd | CompOilLVL_sd | +---------------------+------------+-------...
• 225
215 views

### Help with query

I have following data. I am trying to get the number of days the project stayed in a particular status. Example below of raw data, I need to be able to get the result shown. Can I get some ...
• 61
5k views

### Find continuous records with equal values

I have a table named "TOOLS" . Sample data: ID TAG 1 0 2 1 3 1 4 1 5 0 6 0 7 1 8 1 9 0 So, the SQL query should ...
222 views

### Grouping rows in ranges

Let's say I have the following (contrived) table of monitoring data: RowID DateStamp Success Error ----- ----------------- ------- ----------- 1001 5/...
• 9,732
6k views

### Continuous Date Ranges - Gaps and Islands

CREATE TABLE T1 (Asset_Id int, Trailer_Id int, AssignStart datetime, AssignEnd DATETIME) ; INSERT INTO T1 (Asset_Id, Trailer_Id, AssignStart, AssignEnd) VALUES (37124, 32607, '2018-04-19 ...
• 783
67 views

### Merging two tables by minimal interval values

I have two tables with two columns keeping tabs of categorical values, such as: Table 1 +----+-------+-----+-----------+ | ID | Begin | End | Condition | +----+-------+-----+-----------+ | 1 | 1 ...
2k views

### Postgres lag() function has huge impact on query

As reported to my previous question (that for completeness is reported here), I've solved my problem using the window function LAG to get the time between the previous and next row, sum them and then ...
• 181
240 views

### Counting repeated occurences for a group of fields

I am trying to count the continuous repeated value for a field in order to obtain the maximum repeated occurrences for each person in the table. eg: Time person Result 08:12:36 ...
• 68
161 views

### List chronological pageviews without adjacent duplicates in PostgreSQL

I've got a log of pageviews. Multiple pageviews with the same path may occur next to each other if say the user refreshes the page. To see the user's journey through the site I'd like to select ...
2k views

### Find numbers not used in a column

How can I find in a column filled with numbers, the ones that are not used. Table_A: 'id' int(11) unsigned NOT NULL AUTO_INCREMENT, 'client_code' int(4) unsigned NOT NULL, 'client_name' ...
• 153
2k views

### Find which numbers in [1, 161] are not in the result set?

How to find which two number in [1, 161] is not in the result set? Can I find this two numbers using sql command? mysql> select blog_id from wp_blogs; ... | 149 | | 150 | | 151 | | ...
• 159
5k views

### Getting each status change in a table

I have a table that holds status changes per item. The item can go in and out of a status multiple times. ItemTable (ItemId int, Status varchar(50), DateOfChange date) I need to pull the date of ...
• 631
669 views

### Grouping categorical values in a sequence

I have a table with categorical values in a sequence, as such: CREATE TABLE cat (name, v1, v2) AS VALUES ('John', 1::int, 3::int), ('John', 3, 4), ('John', 4, 9), ('Mike', 9, 11), ...
70 views

### Selecting row grouped by an "add-on" indicator

I have a (pre-existing, no I did not design it like this!) schema / dataset like this: CREATE TABLE t(id,name,seq,addon,kind) AS VALUES ( 1234::int, 'A' , 345::int, 'f'::bool, 'c' ), ( 1235 ...
• 169
5k views

### Calculate number of consecutive years (not gapless)

I have a request that wants to look at numbers of consecutive year buyers. For example, I have a table like this: Order_year, Contact 2015, 123 2014, 123 2013, 123 2011, 123 2010, 123 2015, 234 2014, ...
• 31
3k views

### Stored procedure to check for missing fields in a table

I have a stored procedure that accepts a string parameter, a comma separated list of required column names that the SP should check a particular table and make sure those columns have data in them... ...
• 175
116 views

### How do I aggregate related rows when there's no group key?

I'm using MariaDB 10.6 and have a table: CREATE TABLE velocities ( `state` int(8) NOT NULL, `timestamp` datetime NOT NULL, `velocity` decimal(5,2) NOT NULL, `name` varchar(15) NOT NULL ) ...
• 133