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
1
vote
1answer
40 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-...
1
vote
1answer
229 views

Count of range on datetime in MySql

I want to group table entries for each userId only if they are consecutive entries in the table. I have a table like this. id userId transactionTime 1 1 2016-04-13 22:15:44 2 1 ...
1
vote
1answer
249 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 ...
1
vote
2answers
71 views

Query for condition over time

Say we have a table setup like this: +---------+--------------+---------------+ | id text | time integer | value decimal | +---------+--------------+---------------+ | 1 | 1 | ...
-2
votes
3answers
3k 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 ...
2
votes
1answer
49 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 ...
1
vote
2answers
385 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 ...
0
votes
1answer
35 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 | ...
2
votes
1answer
30 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 ...
-4
votes
1answer
45 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 ...
2
votes
0answers
67 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 ...
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 ...
4
votes
2answers
93 views

Grouping rows in ranges

Let's say I have the following (contrived) table of monitoring data: RowID DateStamp Success Error ----- ----------------- ------- ----------- 1001 5/...
0
votes
1answer
121 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 ...
1
vote
2answers
1k 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 ...
2
votes
4answers
3k 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 ...
0
votes
2answers
88 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-...
0
votes
1answer
38 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 ...
1
vote
1answer
68 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 ...
5
votes
1answer
155 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....
3
votes
2answers
126 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
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 ...
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 (...
-2
votes
1answer
65 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)....
0
votes
0answers
41 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
2
votes
2answers
71 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) --------------------------------------------------------------...
2
votes
2answers
672 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 ...
0
votes
3answers
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 ...
0
votes
1answer
422 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 ...
1
vote
3answers
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 ...
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 ...
1
vote
1answer
295 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 )...
0
votes
1answer
73 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 ...
1
vote
0answers
61 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 ( ...
1
vote
1answer
136 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 ...
5
votes
3answers
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 ...
5
votes
1answer
927 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 ...
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 ...
0
votes
1answer
124 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 ...
0
votes
0answers
119 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 ...
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-...
0
votes
1answer
571 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 ...
2
votes
2answers
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 ----- -------- ...
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 ...
1
vote
1answer
139 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 "...
0
votes
1answer
405 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 ...
8
votes
4answers
10k 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 ...
1
vote
2answers
80 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 ...
2
votes
2answers
77 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), ...
2
votes
2answers
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) );...