We’re rewarding the question askers & reputations are being recalculated! Read more.

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.

Filter by
Sorted by
Tagged with
12
votes
2answers
8k 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 (...
13
votes
7answers
1k views

Grouping or Window

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 ...
3
votes
1answer
5k 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 ...
2
votes
1answer
184 views

identify multiple gaps and islands for n columns

In this previous question, I asked for a way of identifying some gaps and islands. with a table t below; +---------------------+------------+---------------+ | ts | EngOilP_sd | ...
2
votes
2answers
636 views

In what case is a count(x or null) needed in Gaps and Islands?

In this answer, Erwin Brandstetter says: count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern ...
5
votes
3answers
1k 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',...
3
votes
1answer
3k 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 ...
8
votes
1answer
2k 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 ...
8
votes
3answers
9k 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 ...
6
votes
4answers
665 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 ...
5
votes
1answer
559 views

identify gaps and islands

I have a database that has the following structure +---------------------+------------+---------------+ | timestamp | EngOilP_sd | CompOilLVL_sd | +---------------------+------------+-------...
3
votes
1answer
1k views

Calculate Difference in Overlapping Time in PostgreSQL / SSRS

I'm using PostgreSQL 9.2 and SSRS 3.0. I'm trying to calculate the difference in non-overlapping time entries in a timesheet. My data looks like this: I would like this to return: I can get the ...
4
votes
1answer
965 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-...
16
votes
6answers
22k 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 ...
5
votes
2answers
6k 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,...
8
votes
4answers
9k 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 ...
6
votes
1answer
642 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 ...
2
votes
1answer
582 views

How to get max sequence of rows? [duplicate]

I have a table with statistic of games which has next rows (I only write about rows that has interest in this context): id BIGINT PRIMARY KEY date TIMESTAMP WITH TIME ZONE status ...
6
votes
2answers
393 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 ...
6
votes
1answer
4k 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 ...
4
votes
2answers
1k views

How to convert list of dates to list of date ranges grouped by a condition

Given a data set in the below format: State AuditDate 1 2016-11-01 1 2016-11-02 1 2016-11-03 0 2016-11-04 1 2016-11-05 0 2016-11-06 0 2016-11-07 1 ...
4
votes
4answers
4k 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 ...
3
votes
1answer
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 | | ...
3
votes
1answer
213 views

GROUP BY uninterrupted sequence of logs for same location

I'm having a hard time describing my problem or finding it via Google. I want to be able to group rows based on location and employee, but the group should be split if a row with the same location ...
2
votes
1answer
105 views

Computational power required for this query

I am not sure if the query I am trying to run is unreasonably large, in terms of computational power. The query will identify islands (and their duration) of a certain condition in my db. Things work ...
1
vote
1answer
67 views

Combining separate columns into smallest possible related ranges

I'm trying to discard multiple records that may or may not overlap based on the smallest possible contiguous ranges. I thought of doing something similar to This, however the ranges are numeric ...
1
vote
2answers
688 views

Merge date ranges on updates

Given a table of date ranges, e.g. Start Date, End Date, I've seen a few examples online of how to merge ranges on the fly, but I'm able to merge them as the user makes updates. So for example, if 01/...
1
vote
1answer
3k views

Total hours worked per employee per day

I have a table like this: SQLfiddle: http://www.sqlfiddle.com/#!9/340c8 CREATE TABLE Table1 (ID int, empid int, time datetime, state int); +--------------+---------------------+-----------------+ | ...
-3
votes
2answers
2k views

Determine consecutive occurrences of values

I have a table as shown below: CAR NAME INSERT DATE MERCEDES 2018-01-01 SEAT 2018-01-01 MERCEDES 2018-01-02 BMW 2018-01-02 MERCEDES 2018-01-03 MERCEDES 2018-01-04 ...