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
5
votes
3 answers

What is a good way to find gaps in a set of datespans?

What is a way to find gaps in a set of date spans? For example, I have these date spans: 1/ 1/11 - 1/10/11 1/13/11 - 1/15/11 1/20/11 - 1/30/11 Then I have a start and end date of 1/7/11 and 1/14/11. I want to be able to tell that between…
dtc
  • 10,136
  • 16
  • 78
  • 104
5
votes
3 answers

Select rows where price didn't change

Suppose you have a table like (am using SQL Server 2008, no audit log - table is HUGE): SecID | Date | Price 1 1/1/11 10 1 1/2/11 10 1 1/3/11 5 1 1/4/11 10 1 1/5/11 10 Suppose this…
Denis
  • 11,796
  • 16
  • 88
  • 150
5
votes
4 answers

Count equal, consecutive values in an ordered rowset

I have a table with two columns like: CREATE TABLE actions ( action_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, "action" text NOT NULL ); and the following data in it: action_time | action…
Roman Shevtsiv
  • 323
  • 2
  • 5
5
votes
1 answer

TSQL - Groups and Islands dates

I need a help on writing an optimal query for the below problem. Have attached the query I have with me but it is highly utilizing resources. Below is the code to achieve above said logic. Please suggest some optimal way to achieve the same -- drop…
vignesh
  • 1,414
  • 5
  • 19
  • 38
5
votes
2 answers

Postgres GROUP BY looking at dates ranges

I have a table with the history of the "Code" value changes. Every month this table gets a new record with the new value of the "Code" for the specified month. +----------+------------+------------+------+ | Employee | FromDate | ToDate | Code…
Darion Badlydone
  • 897
  • 14
  • 37
5
votes
2 answers

How to merge consecutive date range Oracle

I am facing a problem. I can't figure out how to merge consecutive date range rows together, based on two dimensions. One is OK for me, but second makes troubles Let's imagine table in this structure with four possible scenarios emp_id | level …
Mr.P
  • 1,182
  • 3
  • 20
  • 44
5
votes
1 answer

SQL: Gaps and Islands, Grouped dates

I am trying to group dates within 3 days of each other and assign points based on readmission's within 30 days. A MRN would receive 3 points per readmission. Any help on modifying my query below would be great. Example: CREATE TABLE #z ( …
JBritton
  • 113
  • 6
5
votes
4 answers

Find consecutive free numbers in table

I have a table, containing numbers (phone numbers) and a code (free or not available). Now, I need to find series, of 30 consecutive numbers, like 079xxx100 - 079xxx130, and all of them to have free status. Here is an example how my table looks…
Andrian Nani
  • 63
  • 5
5
votes
2 answers

Find Intersection Between Date Ranges In PostgreSQL

I have records with a two dates check_in and check_out, I want to know the ranges when more than one person was checked in at the same time. So if I have the following checkin / checkouts: Person A: 1PM - 6PM Person B: 3PM - 10PM Person C: 9PM -…
Josh Horowitz
  • 655
  • 2
  • 6
  • 15
5
votes
3 answers

wanted to get all dates in mysql result

I have mysql table called user(id, name, join_on) join on is a date field what I want is to show in each day how many uses has been created I can use group by but it will only give me the dates when users get added like if date 4/12/10 5 users…
Pankaj Khairnar
  • 3,028
  • 3
  • 25
  • 34
5
votes
2 answers

SQL Date Range Query - Table Comparison

I have two SQL Server tables containing the following information: Table t_venues: venue_id is unique venue_id | start_date | end_date 1 | 01/01/2014 | 02/01/2014 2 | 05/01/2014 | 05/01/2014 3 | 09/01/2014 | …
samhankin
  • 93
  • 1
  • 1
  • 8
5
votes
2 answers

SQL Server : Consecutive Rows Issue

I'm having difficulty with a consecutive rows query, and need some assistance. I have a table that is part of a telephony switch. It tracks every employee who logs into a phone (and the phones extension). I have another table that tells me where…
Dave C
  • 7,272
  • 1
  • 19
  • 30
5
votes
5 answers

GROUP BY for continuous rows in SQL

Given the following table: ID State Date 12 1 2009-07-16 10:00 45 2 2009-07-16 13:00 67 2 2009-07-16 14:40 77 1 2009-07-16 15:00 89 1 2009-07-16 15:30 99 1 2009-07-16 16:00 Question:How can i GROUP by…
Kaii
  • 20,122
  • 3
  • 38
  • 60
4
votes
3 answers

Conditional preceding values

My sample data is like this drop table if exists #temp select * into #temp from ( values ('id100', 'status1', 1), ('id100', 'status2', 2), ('id100', 'status1', 3), ('id100', 'status0', 4), ('id100',…
smpa01
  • 4,149
  • 2
  • 12
  • 23
4
votes
2 answers

Gaps and island combined with a recursion

I have a gaps and islands SQL problem combined with a recursion. Background: I run a PostgreSQL database with school vacations and bank holidays from which I render calendar views for schools, city and what not. I code a lot of the logic with the…
wintermeyer
  • 8,178
  • 8
  • 39
  • 85
1 2 3
…
91 92