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
18
votes
7answers
2k 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 ...
16
votes
6answers
21k 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 ...
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 ...
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 (...
12
votes
4answers
1k 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 ...
12
votes
5answers
2k 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'), ('...
10
votes
4answers
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 ...
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 ...
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
2answers
3k 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 ...
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
4answers
5k 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 ...
6
votes
4answers
664 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 ...
6
votes
2answers
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 ...
6
votes
2answers
984 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 ...
6
votes
1answer
314 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-...
6
votes
1answer
630 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 ...
6
votes
2answers
383 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
1k 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 ...
6
votes
1answer
3k 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 ...
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',...
5
votes
3answers
3k 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-...
5
votes
1answer
118 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....
5
votes
1answer
654 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 ...
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,...
5
votes
2answers
6k 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: | ...
5
votes
1answer
179 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 ...
5
votes
3answers
1k 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
votes
2answers
560 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 -> ( -> ...
5
votes
1answer
548 views

identify gaps and islands

I have a database that has the following structure +---------------------+------------+---------------+ | timestamp | EngOilP_sd | CompOilLVL_sd | +---------------------+------------+-------...
5
votes
1answer
637 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 ...
4
votes
1answer
910 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-...
4
votes
2answers
211 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 ...
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 ...
4
votes
2answers
85 views

Grouping rows in ranges

Let's say I have the following (contrived) table of monitoring data: RowID DateStamp Success Error ----- ----------------- ------- ----------- 1001 5/...
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
1answer
47 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 ...
4
votes
1answer
180 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 ...
4
votes
2answers
98 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 ...
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
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 ...
3
votes
3answers
59 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 ...
3
votes
1answer
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... ...
3
votes
2answers
2k 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, ...
3
votes
2answers
112 views

Merge Only Events that Start immediately after the Previous Event without any Gap

How can i merge only the dates occur subsequently.? (i.e, The Next Event begins immediately after the previous event completed). Here, we need to consider some other Columns also for getting the ...
3
votes
2answers
87 views

Grouping Subsets of Rows with Null Values within an Ordered Set

Lets say we have an table where each row is a day, and it is ordered by this day column. Then we have left joined a membership data set showing which day the members were active (and not). Lets say ...
3
votes
1answer
211 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 ...
3
votes
2answers
565 views

Validate id sequence consistency in records in Redshift

I have a backend that log event to Redshift, for each event it generates a unique id. The id is a sequential number. I have something like (events table): +-------------------------+------+ | ...
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 ...
3
votes
3answers
1k 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' ...