Stack Overflow
Stack Overflow

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.

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.

1372 questions
6
votes
2 answers

Filtering out duplicate subsequent records in a SELECT

(PostgreSQL 8.4) Table "trackingMessages" stores tracking events between mobile devices (tm_nl_mobileid) and fixed devices (tm_nl_fixedId). CREATE TABLE trackingMessages ( tm_id SERIAL PRIMARY KEY, -- PK tm_nl_mobileId INTEGER, …
denpanosekai
  • 77
  • 6
6
votes
3 answers

How to identify the first gap in multiple start and end date ranges for each distinct member in T-SQL

I have been working on the below but getting no results and the deadline is fast approaching. Also, there are over a million rows as the below. Appreciate your help on the below. Objective: Group results by MEMBER and build Continuous Coverage…
Vijay
  • 83
  • 1
  • 7
6
votes
1 answer

Find missing time intervals in a table

I have the following table which contains values read every 15 minutes from several different devices: ID DeviceID Date Value ---------------------------------------------- 1 3 24.08.2011 00:00:00 0.51 2 3 …
alex
  • 3,710
  • 32
  • 44
5
votes
5 answers

Oracle: select missing dates

I have a table with (among other things) dates in a field. I need to get a list of all dates that are more recent than the oldest date, older than the most recent date, and are completely missing from the table. So, if the table…
David Oneill
  • 12,502
  • 16
  • 58
  • 70
5
votes
4 answers

How can you find ID gaps in a MySQL recordset?

The issue here is related to another question I had... I have millions of records, and the ID of each of those records is auto-incremented, unfortunately sometimes the ID that is generated is sometimes thrown away so there are many many gaps…
qodeninja
  • 10,946
  • 30
  • 98
  • 152
5
votes
1 answer

How to determine size of continious range for given criteria?

I have a positions table in SQL Server 2008R2 (definition below). In the system boxes there are positions. I have a requirement to find a box, which has X free positions remaining. However, the X positions must be continuous (left to right, top to…
Jonno
  • 789
  • 9
  • 26
5
votes
2 answers

Rank based on cumulative value

I want to rank on ID and value columns based on ascending order of UID. Expected output has to change once value column has a different value than the previous value. Ranks has to restart on each new ID UID ID Value Expected Output 1 1 0 …
Amila
  • 92
  • 2
  • 10
5
votes
8 answers

Fill in gaps in year sequence in SQL Server

I have a table with the columns Age, Period and Year. The column Age always starts with 0 and doesn't have a fixed maximum value (I used 'Age' 0 to 30 in this example but the range could also be 0 to 100 etc.), the values Period and Year only…
SQuirreL
  • 129
  • 1
  • 13
5
votes
4 answers

Delete rows following a duplicate

I have a list of user login and logout stamps. Unfortunately a LOGIN entry might not always be followed by a LOGOUT entry. I wish to delete any row which has the same [event] and [user_id] as previous row when ordered by [event_date] Any suggestions…
ANisus
  • 74,460
  • 29
  • 162
  • 158
5
votes
4 answers

How to make LAG() ignore NULLS in SQL Server?

Does anyone know how to replace nulls in a column with a string until it hits a new string then that string replaces all null values below it? I have a column that looks like this Original Column: PAST_DUE_COL 91 or more days pastdue …
Ryan
  • 87
  • 1
  • 1
  • 6
5
votes
2 answers

Group consecutive rows based on one column

Suppose I have this table from the result of a select * from journeys: timestamp | inJourney (1 = true and 0 = false) -------------------------------------------------- time1 | 1 time2 | 1 time3 | 1 time4 |…
juan_marti
  • 433
  • 1
  • 3
  • 17
5
votes
2 answers

Longest Consecutive Days Count for BigQuery

Right now I just have an aggregate of how many days a user has worked. I'm trying to change this query to most continuous days worked. Where u12345 would be 4 and u1 would be 2. Is this possible to do with a BigQuery statement? EDIT I am Kind of…
bryan
  • 8,879
  • 18
  • 83
  • 166
5
votes
1 answer

Finding gap between date range Postgres

I have this table and need to find gaps between intervals Records may be overlapping. user | start_time | …
ellaRT
  • 1,346
  • 2
  • 16
  • 39
5
votes
3 answers

Producing islands of times throughout a time range. SQL Server

Please help me I have a bit of an odd SQL issue. So if you assume the standard office hours are between 9:00 and 17:00. I have a list of breaks, and I would like to add those breaks to my working day and return a collection of times when I am…
Daniel Frappell
  • 61
  • 3
1 2 3
…
91 92