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

How to group continuous ranges using MySQL

I have a table that contains categories, dates and rates. Each category can have different rates for different dates, one category can have only one rate at a given date. Id CatId Date Rate ------ ------ ------------ …
Fouzi
  • 73
  • 1
  • 4
6
votes
2 answers

SQL GROUP BY: intervals in continuity?

The idea is that say you have the following table. ------------- | oID | Area| ------------- | 1 | 5 | | 2 | 2 | | 3 | 3 | | 5 | 3 | | 6 | 4 | | 7 | 5 | ------------- If grouping by continuity is possible this pseudo…
CBusBus
  • 2,321
  • 1
  • 18
  • 26
6
votes
3 answers

How to generate list of all dates between sysdate-30 and sysdate+30?

Purpose & What I've Got So Far I am attempting to create a view which checks for missing labor transactions. The view will be fed to a Crystal report. In this case, the view should take all dates between sysdate+30 and sysdate -30, and then should…
SeanKilleen
  • 8,809
  • 17
  • 80
  • 133
6
votes
2 answers

SQL Server 2008, temp tables, cursor

I've been working at this for a while. I was wondering how I could get this table: id open_dt ops_hrs 1 10/31/2011 7:00AM - 5:30PM 2 11/1/2011 7:00AM - 5:00PM 3 11/2/2011 7:00AM -…
pdog
  • 61
  • 2
6
votes
2 answers

SQL Find max no of consecutive months over a period of last 12 Months

I am trying to write a query in sql where I need to find the max no. of consecutive months over a period of last 12 months excluding June and July. so for example I have an initial table as…
M_S_N
  • 2,764
  • 1
  • 17
  • 38
6
votes
6 answers

MySQL: Find Missing Dates Between a Date Range

I need some help with a mysql query. I've got db table that has data from Jan 1, 2011 thru April 30, 2011. There should be a record for each date. I need to find out whether any date is missing from the table. So for example, let's say that Feb 2,…
Laxmidi
  • 2,650
  • 12
  • 49
  • 81
6
votes
4 answers

Gaps And Islands: Splitting Islands Based On External Table

My scenario started off similar to a Island and Gaps problem, where I needed to find consecutive days of work. My current SQL query answers "ProductA was produced at LocationA from DateA through DateB, totaling X quantity". However, this does not…
Carson
  • 400
  • 2
  • 15
6
votes
2 answers

How to group timestamps into islands (based on arbitrary gap)?

Consider this list of dates as timestamptz: I grouped the dates by hand using colors: every group is separated from the next by a gap of at least 2 minutes. I'm trying to measure how much a given user studied, by looking at when they performed an…
Fabien Snauwaert
  • 4,995
  • 5
  • 52
  • 70
6
votes
1 answer

SQL Server : finding gaps in employment - island and gap problem

I have been going through stack overflow to try and work this out over the last week and I still can't work out a viable solution so was wondering if anyone could offer me some help/advice? Explanation of the data structures I have the following…
Reky
  • 63
  • 3
6
votes
3 answers

SQL first order, then partition in over clause

I have a problem, that I want to partition over a sorted table. Is there a way I can do that? I am using SQL Server 2016. Input Table: |---------|-----------------|-----------|------------| | prod | sortcolumn | type | value …
Letimogo
  • 542
  • 3
  • 13
6
votes
2 answers

Iterating through dates in SQL

I have a table of data that looks a bit like this: Name StartTime FinishTime Work Bob 2010-08-03 08:00:00 2010-08-03 12:00:00 4 Bob 2010-08-03 13:00:00 2010-08-03 16:00:00 3 Pete 2010-08-04…
JonRed
  • 2,853
  • 5
  • 28
  • 37
6
votes
4 answers

Grouping by consecutive dates in SQL Server

I have table with the following column: [name_of_pos] varchar, [date_from] datetime, [date_to] datetime Below is my sample data: name_of_pos date_from …
Bart
  • 65
  • 1
  • 2
  • 4
6
votes
2 answers

How to add a running count to rows in a 'streak' of consecutive days

Thanks to Mike for the suggestion to add the create/insert statements. create table test ( pid integer not null, date date not null, primary key (pid, date) ); insert into test values (1,'2014-10-1') , (1,'2014-10-2') , (1,'2014-10-3') ,…
Ben
  • 10,931
  • 9
  • 38
  • 47
6
votes
3 answers

Grouping the records on a specific criteria and to find the maximum value

I have a veh_speed table with the fields vid, date_time, speed, status. My objective is to get the duration(start_date_time and end_date_time) of the vehicle with speed greater than 30. Currently I am generating the report using PL/SQL. Is it…
Dba
  • 6,511
  • 1
  • 24
  • 33
6
votes
3 answers

Partially denormalising unicorn observations

There are a number of researchers observing the world's last remaining unicorns, on Easter Island1. Each day the researchers record which unicorn they sighted, the date of the sighting, the number of babies each unicorn has and whether they were…
Ben
  • 51,770
  • 36
  • 127
  • 149
1 2 3
…
91 92