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
145
votes
16 answers

How can we find gaps in sequential numbering in MySQL?

We have a database with a table whose values were imported from another system. There is an auto-increment column, and there aren’t any duplicate values, but there are missing values. For example, running this query: select count(id) from…
EmmyS
  • 11,892
  • 48
  • 101
  • 156
138
votes
22 answers

How do I find a "gap" in running counter with SQL?

I'd like to find the first "gap" in a counter column in an SQL table. For example, if there are values 1,2,4 and 5 I'd like to find out 3. I can of course get the values in order and go through it manually, but I'd like to know if there would be a…
Touko
  • 11,359
  • 16
  • 75
  • 105
128
votes
19 answers

SQL to determine minimum sequential days of access?

The following User History table contains one record for every day a given user has accessed a website (in a 24 hour UTC period). It has many thousands of records, but only one record per day per user. If the user has not accessed the website for…
Jeff Atwood
  • 63,320
  • 48
  • 150
  • 153
100
votes
23 answers

Get a list of dates between two dates

Using standard mysql functions is there a way to write a query that will return a list of days between two dates. eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values: 2009-01-01 2009-01-02 2009-01-03 …
Gilgad
  • 1,137
  • 4
  • 10
  • 10
81
votes
7 answers

MySQL how to fill missing dates in range?

I have a table with 2 columns, date and score. It has at most 30 entries, for each of the last 30 days one. date score ----------------- 1.8.2010 19 2.8.2010 21 4.8.2010 14 7.8.2010 10 10.8.2010 14 My problem is that some dates are…
Jerry2
  • 2,955
  • 5
  • 30
  • 39
56
votes
16 answers

SQL query to find Missing sequence numbers

I have a column named sequence. The data in this column looks like 1, 2, 3, 4, 5, 7, 9, 10, 15. I need to find the missing sequence numbers from the table. What SQL query will find the missing sequence numbers from my table? I am expecting results…
GiriYahoo
46
votes
16 answers

Find the smallest unused number in SQL Server

How do you find the smallest unused number in a SQL Server column? I am about to import a large number of manually recorded records from Excel into a SQL Server table. They all have a numeric ID (called document number), but they weren't assigned…
Michael La Voie
  • 27,772
  • 14
  • 72
  • 92
33
votes
9 answers

Detect consecutive dates ranges using SQL

I want to fill the calendar object which requires start and end date information. I have one column which contains a sequence of dates. Some of the dates are consecutive (have one day difference) and some are not. InfoDate 2013-12-04 consecutive…
Shamim
  • 461
  • 1
  • 7
  • 14
31
votes
5 answers

How do I group on continuous ranges

I know some basic SQL, but this one is beyond me. I have looked high and low but no dice. I need a view of the following data, I can do this in the application layer code. But unfortunately for this particular one, the code must be put in the data…
Luke
  • 313
  • 1
  • 3
  • 4
30
votes
6 answers

MySQL: Select All Dates In a Range Even If No Records Present

I have a database of users. I would like to create a graph based on userbase growth. The query I have now is: SELECT DATE(datecreated), count(*) AS number FROM users WHERE DATE(datecreated) > '2009-06-21' AND DATE(datecreated) <= DATE(NOW()) GROUP…
Jason
  • 51,583
  • 38
  • 133
  • 185
27
votes
11 answers

How to check any missing number from a series of numbers?

I am doing a project creating an admission system for a college; the technologies are Java and Oracle. In one of the tables, pre-generated serial numbers are stored. Later, against those serial numbers, the applicant's form data will be entered. My…
Samcoder
  • 345
  • 1
  • 4
  • 14
21
votes
5 answers

SQL Query to show gaps between multiple date ranges

Im working on a SSRS / SQL project and trying to write a query to get the gaps between dates and I am completely lost with how to write this.Basically we have a number of devices which can be scheduled for use and I need a report to show when they…
Purplegoldfish
  • 5,268
  • 9
  • 39
  • 59
21
votes
3 answers

sql group by only rows which are in sequence

Say I have the following table: MyTable --------- | 1 | A | | 2 | A | | 3 | A | | 4 | B | | 5 | B | | 6 | B | | 7 | A | | 8 | A | --------- I need the sql query to output the following: --------- | 3 | A | | 3 | B | | 2 | A | --------- Basically…
Pavel
  • 729
  • 2
  • 11
  • 22
20
votes
3 answers

Trouble using ROW_NUMBER() OVER (PARTITION BY ...)

I'm using SQL Server 2008 R2. I have table called EmployeeHistory with the following structure and sample data: EmployeeID Date DepartmentID SupervisorID 10001 20130101 001 10009 10001 20130909 001 10019 10001 …
Thracian
  • 651
  • 4
  • 8
  • 24
19
votes
1 answer

Resetting Row number according to record data change

I have got the set of data as follow name date x 2014-01-01 x 2014-01-02 y 2014-01-03 x 2014-01-04 and I'm trying to get this result name date row_num x 2014-01-01 1 x 2014-01-02 2 y 2014-01-03 …
erezlale
  • 625
  • 2
  • 6
  • 17
1
2 3
…
91 92