The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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

113 questions
Filter by
Sorted by
Tagged with
22 views

### GROUP BY with date ranges when values change but then change back

Consider a historical table where a value was changed and then changed back: value | date 3 | 1/1/2019 3 | 1/2/2019 3 | 1/3/2019 4 | 1/4/2019 4 | 1/5/2019 3 | 1/6/2019 3 | ...
26 views

### Add Gameweek Fixeture based on Datefield

having the following table i need to add a new Column called "Round" to represent Round 1, Round 2 and so on, based on the Date Column in the table. Same Round should be given to matches happening in ...
44 views

### How to get the users who viewed a video on 95%?

There is a table with viewed chunks. video_id | user_id | start_second | end_second 1 | 1 | 0 | 10 1 | 1 | 0 | 20 1 | 2 | 1 ...
58 views

### How to perform a running count on null values that resets on non-null values

I am trying to calculate a column that performs a running count of consecutive null values, but the running count will reset upon non-null values. Currently I am trying to do it with a window function ...
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 ...
202 views

### How to use windowing functions to find gaps in sequences grouped by a column, in PostgreSQL

I have a table structure which has 2 columns: client_id, order_no, and both are integers. I'd like to find gaps in order_no in the data so that, for example, for a table containing these rows: 42, 1 ...
330 views

### Find termination and rehire dates

We have a time history table named PRTH that has EmployeeID, PostDate (this is the day worked), and hours. Our ERP system does not track termination and rehire date history so I have been asked to ...
85 views

### Grouping rows in ranges

Let's say I have the following (contrived) table of monitoring data: RowID DateStamp Success Error ----- ----------------- ------- ----------- 1001 5/...
113 views

### Count Consecutive Codes

I'm searching for a query (cannot figure it out myself at the moment with the help of already found queries around here. The quest I have is the following one: I have a view that gives following ...
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 ...
895 views

### Find the OverLapped (Common) Date Range from Group of Date Ranges

How can I find the Overlapped (common date range) from a group of Date Ranges given? The following table contains events and programmes (EID and PID respectively) CREATE TABLE #EventsTBL ( PID ...
2k 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 ...
55 views

### Mysql consecutive days user wise

Table 'present' +--------+------------+ | emp_id | pre_date | +-----------+---------+ | 002 | 2019-04-01 | | 001 | 2019-04-21 | | 001 | 2019-04-15 | | 001 | 2019-04-19 | | 002 | 2019-...
36 views

### How to select blocks from a result by condition?

Example table: master_id 1 created_at 22.02.1997 master_id 1 created_at 22.03.1997 master_id 1 created_at 22.04.1997 master_id 1 created_at 22.07.1997 master_id 1 created_at 22.08.1997 master_id 1 ...
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 ...
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....
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 ...
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 ...
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 (...
57 views

### SQL Server Database flatten records

Sorry for the confusion. I have below attached table sql server database table holding continuous and non continuous records. When i say continuous that means EP of first record if Beginning Point(BP)....
35 views

### how to counting time in partition (MYSQL)

I looking for how to counting time by partition in condition if value=1 occurs consecutively 3 or more times count 1 activity. Showing result on this picture
67 views

### How to select blocks with sequential data and aggregate the ids

I have the following table: id(int) startDate(timestamp) endDate(timestamp) plan_id(int) planned(bool) machine(int) --------------------------------------------------------------...
625 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 ...
3k views

### Find the missing numbers in a given range

Hi I have a table that has start and end columns that has the values Start End 9 10 11 19 29 44 45 51 56 60 Now if I give the range like 15 to 60 I need to return the ...
341 views

### Consecutive dates count

I have table data like below: CLIAM_KEY MBR_ID SERV_FROM clm1 mem1 01-JAN-18 clm2 mem1 02-JAN-18 clm3 mem1 03-JAN-18 clm3 mem1 04-JAN-18 clm4 mem1 ...
1k views

### Select only neighbour rows who pass a where-test

I want to apply a selection process to my data, that will not include rows which should be accepted by my WHERE clause but who follow after a row which fails the WHERE. Example. I have a list of gps ...
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 ...
233 views

### Find missing timestamps grouped by key: finding gaps in my data

I have a table that has a timestamp, some data and a identifying key for the data source: create table test_data ( id serial primary key, key text, timestamp timestamp with time zone )...
68 views

### Add running count of specific state changes

I have a table in PostgreSQL with below fields, where timestamp is simplified as integers: ts status 1 m 2 m 3 i 4 s 5 s 6 i 7 i 8 m 9 s 10 m I want to break ...
60 views

### Finding the largest island size (redshift)

Current sql code will sum up all the max island sizes into one. I'm trying to only find what the max island size is. select deviceid, grp -- min(readdate) as mn, max(readdate) as mx from ( ...
127 views

### How to get row_number() within a group?

I have a table event_seating_lookup with 3 columns: (id int, event_id int, lookup_is_successful boolean). For every event_seating_lookup record, I want to get a number of previous (lag) records with ...
2k 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 ...
647 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 ...
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 ...
118 views

### Count days between dates and find largest gap

I need to find all members in our database from a calendar month that do not have a gap of 10+ days between sending data / or in the month total i.e. everyone in April 2018. The table is set up like ...
117 views

### Find consecutive entries in a table and group into another table

So ideally I'd like a table output such as: consecutive_days | max_date for a given user where consecutive days is the number of days a person logged something and max_date was the last date in ...
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-...
446 views

### How to group records by a two-value column ignoring small gaps?

I have a subquery with a column named code that only has 1 and 0 values and an ID column because I query a lot of codes for each ID. I want to group by said column (and by ID) and by ocurrence but ...
2k views

### Merging adjacent date intervals into a single row

Here is the scenario: If the employee transaction is continuous display as-is. if the employee got any gap display as a new row. Below is the sample table Emid Fromdate Todate ----- -------- ...
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 ...
127 views

### sqlite - Distinguish a column with the same value by range of id and count this range, take the higher one

I have a small sqlite table CREATE TABLE IF NOT EXISTS intersection ( cnt NUMERIC NOT NULL, sweep NUMERIC) from what i want to get the maximum value of "cnt" and later on the average of "...
358 views

### Finding gaps-and-islands with repeating group numbers

We have a MS-SQL dataset of tests run in various groupings and need to uniquely identify the groups sr is the device identifier tx is the TrackingIndex (insert order) id is the loop order of each ...
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 ...
77 views

### How can I assign group IDs depending on content?

I have the following table with data as shown below; linenrs are monotonically increasing but not necessarily consecutive; when the key field contains an ellipsis ... that indicates an entry that is ...
70 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), ...
1k views

### Window function to merge rows that meet a condition

I have the following schema DROP TABLE IF EXISTS messages; DROP TABLE IF EXISTS chats; CREATE TABLE chats ( client integer NOT NULL, provider integer NOT NULL, PRIMARY KEY (client, provider) );...
98 views

### Order by for 2 row result causes 800% performance hit

The following query, run against a table of about 6 mil rows takes 25 seconds. When I remove the final order by, it runs in 3 seconds. There are no indexes on the table (it's an intermediate SSIS ETL ...
611 views

### Group rows by uninterrupted dates

I want to return data in such way that the data which have if code has continuous active dates and end dates it should give Min as active date and Max end date, but if code was discontinued it should ...