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
2 votes
2 answers
38 views

Finding gaps between the values of a column in MySQL

At a table of movie actors, CREATE TABLE ArtistMap ( ArtistID int(11) unsigned NOT NULL, MovieID int(11) unsigned NOT NULL, Year year, INDEX(MovieID), INDEX(Year), PRIMARY KEY(ArtistID,MovieID) ) ...
user avatar
  • 4,179
2 votes
1 answer
63 views

Get rows grouped by a foreign key with count of consecutive values

I have a SQL Server database with a transactions table with client_id, date, and is_cancelled. I'm trying to get the client_ids that have 3 or more transactions in a row marked as is_cancelled, along ...
user avatar
0 votes
1 answer
71 views

Retrieve gaps in dates in SQL Server

I'm trying to figure out how to retrieve gaps in time-series data. Below is my example. CREATE TABLE MYTABLE ([ID] int, [DATE] DATE) ; INSERT INTO MYTABLE ([ID], [DATE]) VALUES (1, '2022-...
user avatar
0 votes
1 answer
39 views

Possible to GROUP BY across different columns in mariadb/mysql

I have a table full of events similar to this: `date` | start_time | end_time | length -------------------------------------------- 2022-01-10 | 07:00:00 | 16:00:00 | 9 2022-01-11 | 07:00:...
user avatar
1 vote
1 answer
142 views

Postgres find gaps between one date range and others

My question is similar to https://stackoverflow.com/a/41267928/2585154 , but in my case I need to divide/split date range by multiple date ranges, not only one. In my case is also guaranteed that &...
user avatar
  • 123
2 votes
1 answer
98 views

T-SQL : Slicing time periods by related time periods

Consider the following table that gives the validity dates for which an Item is associated to something called a Demand_Unit, and a certain Market : Item_ID Start_Date End_Date Demand_Unit Market X ...
user avatar
  • 23
0 votes
1 answer
77 views

T-SQL : Grouping based on chronological switch periods

I have the below table extracted from a Type 2 Slowly Changing dimension. I want to group rows to only have distinct classifications and their respective 'switch' time periods in a chronological order....
user avatar
  • 23
1 vote
2 answers
70 views

How to select the latest continuous set of rows of a certain type?

(Using PostgreSQL) I'm looking to select the latest set of continuous (by date) rows that have a code 'A'. The latest row is always missing the "To Date" as it is assumed to be current. And ...
user avatar
3 votes
1 answer
71 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 ) ...
user avatar
  • 133
0 votes
1 answer
59 views

Determine Available Blocks of Contiguous Ids

We have a SQL Server table that has an int autoincrement primary key column on it. The table's primary key space is fragmented. For example, ids 1 through 10 are in use but then ids 11 through 100,000 ...
user avatar
0 votes
1 answer
63 views

Count of null rows preceding each non-null value

I need to answer the following question: What is the average number of email sends it take before a customer opens an email? I know I need to count the number of rows between each Open_Date in order ...
user avatar
  • 3
1 vote
1 answer
231 views

Pick the first timestamp before a gap, but the last one of the day if there is no suitable gap

I have a TIMESTAMP column: dates 2021-06-24 05:47:05 2021-06-24 09:47:05 2021-06-24 13:47:05 2021-06-24 17:47:05 I want to pick the first timestamp of a given day that is 3 hours or more before the ...
user avatar
0 votes
1 answer
40 views

how to generate a sequence of values with differents formats in PostgreSQL

I want to find rows missing from a sequence of values in my table with differents formats. For an example, look at this. I want to find the missing lines via c_order_id and documentno. the one with '...
user avatar
0 votes
2 answers
128 views

How to find values missing from a sequence?

I want to find rows missing from a sequence of values in my table. For example, in this picture documentno 'YD4BC2006008' is missing. I want to find the missing lines via c_order_id and documentno. I ...
user avatar
0 votes
2 answers
42 views

Detect multiple valid date periods for same id, interleaved with non-valid periods

My table structure and some sample data (test table), as well as my desired output (results) are available here. When I do SELECT distinct colony, min(date_check), max(date_check) from test where ...
user avatar
  • 17
3 votes
1 answer
338 views

Mysql get records more then 3 in interval of 1 minute, return group_concat ID

Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3. CREATE TABLE test ( `id` bigint NOT NULL AUTO_INCREMENT, created_date ...
user avatar
  • 239
3 votes
1 answer
534 views

How to collapse rows with individual, contiguous dates into single rows with date range start and end

Given a data set like this: Id Date Value 1 01/01/2021 100 1 01/02/2021 100 1 01/03/2021 100 ... ... (rows omitted for brevity) ... 1 12/29/2021 100 1 12/30/2021 100 1 12/31/2021 100 2 01/01/...
user avatar
1 vote
1 answer
72 views

Query to find if there are more than X occurences within any period of given length minutes

I have spent many hours thinking about a solution for my problem but I give up. Let's imagine a table user_id | occurred_at -- OK match example 1 | 2020-01-01 08:00:00 <- First match of ...
user avatar
  • 13
2 votes
2 answers
135 views

Gaps and Islands

I have a table with StartDate and EndDate. I wanted to get the missing Date and The script I have does it. But if given this data: LocationID StartDate EndDate ---------- ---------- ---------- ...
user avatar
  • 23
3 votes
2 answers
980 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 ...
user avatar
  • 171
0 votes
1 answer
78 views

Calculate difference of days between dates in different rows, based on consecutive value of a column, for each customer_id

I have a table like this one, that shows payments from each client, for different dates: | stream_datetime | customer_id | order_status | rn | |:----------------:|:-----------:|:------------:|:--:| | ...
user avatar
  • 111
2 votes
2 answers
123 views

Group by time interval and output the source and destination station_id and count

I am stuck with a query: CREATE TABLE public.bulk_sample ( serial_number character varying(255), validation_date timestamp, -- timestamp of entry and exit station_id integer, ...
user avatar
1 vote
1 answer
198 views

SQL Server - Get the Date Ranges

I have a table with start date and end date. For example, PlaceID | StartDate | EndDate 1 | 2020-01-01 | 2020-01-04 1 | 2020-01-10 | 2020-01-15 2 | 2020-01-03 | 2020-01-06 2 | ...
user avatar
0 votes
1 answer
336 views

How to collapse contiguous single day start/end date ranges in MySQL

(edited - I oversimplified the original problem) I'm trying to figure out a MySQL query that will collapse one-day long contiguous start/end date ranges. Here is my sample input data: INSERT INTO ...
user avatar
  • 71
0 votes
1 answer
54 views

How do I find the first row of the longest occurrences of repeating numbers?

I have a table with the following structure: id,center,shelf_id,occupied,remaining 1,East,1_1_1,1,0 2,East,1_1_2,1,0 3,East,1_1_3,1,4 4,East,1_1_4,1,0 5,East,1_1_5,1,0 6,East,1_2_1,1,3 7,East,1_2_2,1,...
user avatar
0 votes
1 answer
75 views

Gaps and Islands Across Fields With Start and End Timestamps

I am working with GPS data which I want to group by locations (to six decimal places of the coordinate value) and generate from and until times for. For simplicity in this example I'm making the ...
user avatar
  • 101
0 votes
1 answer
34 views

Need to identify non contigious rows

My table structure is as follows: create table daily_summary ( id uuid default uuid_generate_v4() not null primary key, machine bigint ...
user avatar
0 votes
1 answer
53 views

MSSQL - convert list of datetimes in a date range

I have one system which stores data like: CREATE TABLE Reservations ([UserId] int, [RoomId] int, [TypeId] int, [Date] datetime) ; Every row in the table means that Room is reserved for User of ...
user avatar
  • 23
0 votes
1 answer
72 views

Gap Finding - Dates

Essentially i face a islands & gaps problem but unlike many of the examples i found i do not have a unique ID to group over to find my gaps. Data is timestamp without timezone formatted and an ...
user avatar
1 vote
1 answer
54 views

Merging Continous Date Ranges with upto 63 days in between

I am trying to write a query that will take multiple date ranges and combine them into a contiguous date range. The catch is that I have to allow for up to 63 days in between the last termination date ...
user avatar
  • 27
1 vote
1 answer
96 views

Determine Order Streak

For a particular customer, I have data returned by a query that looks like this: OrderDate OrderCount 2020-01-12 0 2020-01-13 17 2020-01-14 22 2020-01-15 21 2020-01-16 30 2020-01-17 12 2020-...
user avatar
  • 113
1 vote
2 answers
101 views

Query for condition over time

Say we have a table setup like this: +---------+--------------+---------------+ | id text | time integer | value decimal | +---------+--------------+---------------+ | 1 | 1 | ...
user avatar
  • 125
2 votes
1 answer
79 views

Gaps and islands with GPS breadcrumb data

I've got a gaps and islands (GAI) problem that I can't seem to crack to save my life. Most of the GAI problems I've seen involve data in the form of: [Id] [StartDateTime] [EndDateTime] But mine in ...
user avatar
0 votes
1 answer
137 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 | ...
user avatar
2 votes
1 answer
32 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 ...
user avatar
  • 33
-4 votes
1 answer
57 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 ...
user avatar
  • 93
2 votes
0 answers
132 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 ...
user avatar
4 votes
2 answers
192 views

Grouping rows in ranges

Let's say I have the following (contrived) table of monitoring data: RowID DateStamp Success Error ----- ----------------- ------- ----------- 1001 5/...
user avatar
  • 9,584
0 votes
1 answer
134 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 ...
user avatar
  • 101
0 votes
2 answers
779 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-...
user avatar
0 votes
1 answer
45 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 ...
user avatar
  • 3
5 votes
1 answer
425 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....
user avatar
3 votes
2 answers
176 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 ...
user avatar
-2 votes
1 answer
172 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)....
user avatar
  • 1
1 vote
1 answer
88 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 ...
user avatar
  • 113
0 votes
0 answers
56 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
user avatar
2 votes
2 answers
123 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) --------------------------------------------------------------...
user avatar
1 vote
2 answers
4k 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 ...
user avatar
2 votes
1 answer
770 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 ...
user avatar
0 votes
1 answer
928 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 ...
user avatar