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
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 ...
2
votes
1answer
112 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 ...
9
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 ...
2
votes
1answer
195 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 | ...
3
votes
1answer
229 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 ...
5
votes
1answer
612 views

identify gaps and islands

I have a database that has the following structure +---------------------+------------+---------------+ | timestamp | EngOilP_sd | CompOilLVL_sd | +---------------------+------------+-------...
1
vote
3answers
184 views

Finding “islands” of holiday periods for employees

I'm looking for some tips to find a nice solution for my problem: Let's say I have a table, where days of vacation of employees are stored. e.g. CREATE TABLE test_dates ( id NUMBER ...
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 ...
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, ...
1
vote
1answer
819 views

Group by maximum consecutive row

I have data like the following: id | src_ip | dst_ip | port | ----|----------------|--------------|------| 256 | 192.168.61.200 | 10.75.64.222 | 80 | 257 | 192.168.61.200 | 10.75.64....
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,...
6
votes
1answer
321 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-...
1
vote
1answer
272 views

Count of 3 consecutive unsynced dates

I have a table set out like so: user_id the_date minutes_asleep ---------------------------- 1 2015-01-01 480 1 2015-01-02 0 1 2015-01-03 0 1 2015-01-04 0 ...
4
votes
1answer
183 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 ...
1
vote
3answers
7k views

Split date/time range into day ranges [closed]

Using this data (also shown in this fiddle): create table dbo.records ( id int not null primary key identity(1, 1), startdatetime datetime not null, enddatetime datetime not null ); insert ...
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' ...
6
votes
2answers
1k 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 ...
8
votes
3answers
10k 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 ...
2
votes
3answers
2k views

Find and remove gaps in sequence across two different columns

I've tried to solve this problem using a set-based approach. However, since I need to look at each row I think I must use a cursor; please correct me if I'm wrong. The table: Project, item, method, ...
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 ...
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
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 | | ...

1 2
3