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.
Questions tagged [gaps-and-islands]
166 questions
21
votes
6 answers
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 ASSIGNED
1 000004 FREE
1 000005 FREE
1 …
boobiq
- 827
- 3
- 9
- 13
20
votes
7 answers
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)
VALUES
( '2017-01-10 07:19:21.0', 3 ),
(…
Lelo
- 303
- 2
- 6
18
votes
7 answers
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 of dates (weekends, public holidays and longer…
Vladimir Baranov
- 4,720
- 4
- 27
- 42
14
votes
2 answers
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 (enum), race_id (int FK)
Where lap_no is unique for…
DaveB
- 319
- 2
- 6
- 16
12
votes
5 answers
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ᵀᴹ
- 31,952
- 10
- 86
- 108
12
votes
4 answers
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 item 20010 is between these days we will not add…
AA.SC
- 4,073
- 4
- 28
- 45
11
votes
4 answers
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 2015-01-05
1 a 2015-01-06 2015-01-10
1 …
hazrmard
- 255
- 3
- 5
- 13
10
votes
4 answers
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
PRIMARY KEY
) ;
GO
INSERT INTO dbo.Numbers
…
A-K
- 7,444
- 3
- 35
- 52
10
votes
1 answer
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 2017-10-18 03:25:28 idle
Brown 2017-10-18…
user3719749
- 103
- 1
- 1
- 5
10
votes
1 answer
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 for example data (example uses…
Evan Carroll
- 65,432
- 50
- 254
- 507
10
votes
1 answer
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 reassigning the ids in such a way that the original id…
rubik
- 535
- 7
- 18
9
votes
3 answers
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
749
the resulting data with a consecutive row…
OrangeYoda
- 91
- 1
- 1
- 2
9
votes
2 answers
Generate a series of dates for each group in a table
I have a balances table in PostgreSQL 9.3 that looks like this:
CREATE TABLE balances (
user_id INT
, balance INT
, as_of_date DATE
);
INSERT INTO balances (user_id, balance, as_of_date) VALUES
(1, 100, '2016-01-03')
, (1, 50, '2016-01-02')
,…
Shaun Scovil
- 341
- 1
- 5
- 11
8
votes
3 answers
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 NULL
)
INSERT INTO @Data (Sequence, Subset)…
Jon of All Trades
- 5,987
- 7
- 48
- 63
8
votes
2 answers
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 column values are integers it only looks for…
Link
- 183
- 1
- 4