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
0
votes
2 answers

Finding gaps in non-sequential date ranges

I'm am trying to find a way to compare a serives of data ranges to find gaps, however i need to exclude date ranges that are wholly within another range. Some example data: PERSON_ID START_DATE END_DATE 0001 01/05/2014 30/11/2014 0001 …
Chizo Ejindu
  • 58
  • 6
0
votes
2 answers

SQL: Identify distinct blocks of treatment over multiple start and end date ranges for each member

Objective: Identify distinct episodes of continuous treatment for each member in a table. Each member has a diagnosis and a service date, and an episode is defined as all services where the time between each consecutive service is less than some…
Uncle Milton
  • 163
  • 1
  • 3
  • 10
0
votes
2 answers

select min and max values before a particular row per customerID SQL

I have a table which has info about visitors to our website. So it will look like VISITOR_ID EVENT Date Rank ( I already ranked them portioned by event) Visitor_id Event Date rank 1 visit 1/1/14 1 1 purchase …
user3571236
  • 3
  • 3
0
votes
1 answer

Grouping smallest time per group in SQL

This is a very simple question and I feel like the answer should be obvious, but it's been two hours I'm not finding a solution. I am willing to transform discrete data flux stored in PostgreSQL. They are originally stored as high frequency samples.…
Diane M
  • 1,503
  • 1
  • 12
  • 23
0
votes
2 answers

T-SQL - Data Islands and Gaps - How do I summarise transactional data by month?

I'm trying to query some transactional data to establish the CurrentProductionHours value for each Report at the end of each month. Providing there has been a transaction for each report in each month, that's pretty straight-forward... I can use…
triplestones
  • 393
  • 2
  • 13
0
votes
2 answers

Delete rows with specific values in one column and a missing value in another column of the same or consecutive row

My dataset consists of daily timeseries for different companies and I work with PostgreSQL. I have a indicator variable in my dataset, taking values 1, -1 and most of the time 0. If the indicator variable is not 0, and a company has a missing value…
user3319629
  • 111
  • 1
  • 11
0
votes
2 answers

Detect and delete gaps in time series

I have daily time series for different companies in my dataset and work with PostgreSQL. My goal is to exclude companies with too incomplete time series. Therefor I want to exclude all companies which have 3 or more consecutive missing values.…
user3319629
  • 111
  • 1
  • 11
0
votes
3 answers

Identifying the boundaries of N-groups

I have spent quite some time dealing with the following: Imagine that you have N number of groups with multiple records each and every record has unique starting and ending points. In other…
Kiril Rusev
  • 745
  • 3
  • 9
0
votes
1 answer

row_number numbering arbitrary value restart for value change

I have a table xxx with the following structure and columns (SQL Fiddle) CREATE TABLE [dbo].[xxx] ( [GameNo] [int] IDENTITY(1,1) NOT NULL, [GameID] [varchar](500) NULL, [UserID] [int] NULL, [SteamID] [nvarchar](50) NULL, …
balaji dileep kumar
  • 622
  • 7
  • 12
0
votes
1 answer

How to find groups of sequential integers in Oracle?

I am using oracle 10g. My (simplified) table definition is CREATE TABLE Student ("Rno" INT PRIMARY KEY) ; Which contains the following rows | RNO | |-----| | 1 | | 2 | | 3 | | 6 | | 8 | | 9 | | 12 | | 13 | | 14 | | 18 | How can…
user3090870
  • 35
  • 2
  • 6
0
votes
1 answer

Fill in missing dates for multiple values with a GROUP BY

I'm trying to write a single query for retrieving time information for multiple users over a date range. My tables are set up as follows: Entry: user_id | entry_date | entry_hours 1 | 2013-11-12 | 4 1 | 2013-11-12 | 5 1 |…
Samsquanch
  • 8,866
  • 12
  • 50
  • 89
0
votes
1 answer

How can I create a query using a range of dates and count per month?

I'm trying to create a range to my policies Here is my table |policies| |id| |date_ini| |date_end| 1 2013-01-01 2014-01-01 2 2012-02-01 2013-02-01 3 2013-03-01 2013-03-03 4 2013-04-01 …
Carlos Morales
  • 1,137
  • 3
  • 15
  • 38
0
votes
3 answers

sum two rows and order by date / total

need some help to build a query, this is my current scheme: users: +----+------------+ | id | username | +----+------------+ | 1 | rob | | 2 | john | | 3 | jane | <--- jane never has donated | 4 | mike |…
greenbandit
  • 2,267
  • 5
  • 30
  • 44
0
votes
1 answer

PostgreSQL: Free time slot algorithm

I have a table with some time slots in it, example: #id datet userid agentid duration +=======================================================+ |1 |2013-08-20 08:00:00 |-1 |3 |5 |2 |2013-08-20 08:05:00 |-1 |3 |5 |3 …
Write Down
  • 158
  • 2
  • 5
  • 15
0
votes
1 answer

Select between times, fill in null hours

I have a database here | TIME | STOCK |9:00 |24 |12:00 | 15 I want to display the time 6am now until 4am tomorrow I want to put it in a mysql query where i will put null in specific time that not similar in my database like this ID | STOCK 6:00…
student
  • 181
  • 1
  • 2
  • 8
1 2 3
…
91 92