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
-1
votes
1 answer

Get dates missing from multiple date ranges

I have one table that stores when a customer support employee is in a particular location and for what date. Each separate date is its own record. I have a second table that stores a range of dates that customers have asked for onsite support. I…
Goolsy
  • 237
  • 3
  • 14
-1
votes
2 answers

Grouping multiple periods into one period

I have a list of time frames. Each record has an ID, Start and End date/time. Several records are back to back. Meaning the Ending Time is the starting time of the next record. I need to join these records together to form one long period. I have…
frmrock164
  • 47
  • 11
-1
votes
3 answers

merge data rows if they have sequential time intervals

I have a dataset for patient medications with Start.Date and Stop.Date. Each is represented in a row. I would like to merge rows where the time intervals are sequential as below: ID = c(2, 2, 2, 2, 3, 5) Medication = c("aspirin", "aspirin",…
gfa2001
  • 227
  • 1
  • 2
  • 10
-1
votes
1 answer

How to retrieve only the records where stat changes?

I want to get same output: using the following sample data create table x ( id int, date datetime, stat int ) insert into x values (1, '2017-01-01', 100), (1, '2017-01-03', 100), (1, '2017-01-05', 100), (1, '2017-01-07',…
Amy
  • 3
  • 3
-1
votes
1 answer

SQL Server - Find Missing Numbers in sequence where numbers contain preceding zeros

I have numbers in sequence stored in database which are stored as text and contain preceding zeros. Numbers ------- 001 002 003 004 006 007 010 011 ------- The query should find following result Missing ------- 005 008 009 ------- Thanks.
sourabhgk
  • 99
  • 4
  • 12
-1
votes
2 answers

SQL Create sequence of rows according to time difference

I'm trying to do the following thing: I have table with users activities in system, and I want to create a sequence of activities according to the time of each activity on each object and when the time between each activity is up to 10 seconds, and…
nlan
  • 7
  • 3
-1
votes
1 answer

counting islands in R along rows in csv

I asked this question previously, and Frank answered it here. Original question: I would like to count islands along rows in a .csv. I say "islands" meaning consecutive non-blank entries on rows of the .csv. If there are three non-blank entries…
agrobins
  • 109
  • 1
  • 7
-1
votes
2 answers

Query to find ranges of consecutive rows

I have file that contains a dump of a SQL table with 2 columns: int ID (auto increment identity field) and bit Flag. The flag = 0 means a record is good and the flag = 1 means a record is bad (contains an error). The goal is to find all blocks of…
sdoan_
  • 1
-2
votes
3 answers

Postgresql problems

i have a table like this: name used time asd 10 15:00 bsf 15 15:00 asd 20 14:55 bsf 0 14:55 i need to make a query that returns values like this: the result i need for the grafana timeseries is: total …
Mauricio Del Aguila
  • 13
  • 4
-2
votes
1 answer

Gaps and Islands but with a lot of nulls

I have source data like following table and trying to get desired output. Basically I want to running total until status changes to…
Poojan Patel
  • 19
  • 5
-2
votes
2 answers

SQL Add Medication days and find gaps

I need add the days of medication for people over the course of a year and determine how many days were covered by medication. This is similar to a Gaps and Islands problem except that people don't wait till the last day of their previous…
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
-2
votes
1 answer

SSMS rolling count based on value resetting at 0

Trying to get a rolling count that resets when 0 is hit. Only need a few fields. Ordered by the date field and then ref field when value goes 0 to >0 count begins at 1 If value remains same or increases count goes to 2 and beyond if value goes to 0…
Tom_Foolery
  • 1
  • 1
-2
votes
2 answers

Find the missed call and call back from the below table using SQL

I have question which I'm not able to solve, can anyone please suggest the approach? From To Start End 9901234567 8854320145 10:00 10:00 9435678911 7657894335 10:30 10:33 8764091245 9765487678 09:45 09:47 8854320145 …
JG1
  • 1
  • 2
-2
votes
1 answer

How to use Pandas to solve Gap and Island problem with dates?

I am hoping someone out there can help. I am trying to figure out how to solve gap and island problems with pandas, but have been unsuccessful. For a sample data set I am using: np.random.seed(1066) dates = pd.date_range(start='2010-01-01',…
pandas_newb
  • 3
  • 1
-2
votes
1 answer

Need to calculate running total

I have data like below and I need to somewhat generate running total but not really. When value changes from Green to either (Yellow or Red) then 1 for all rows until it changes again from Green to either Red or Yellow then 2 for all rows until it…
user3456025
  • 1
  • 3
1 2 3
…
91 92