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

I'm trying to build a view in SQL which identifies and groups records with consecutive date ranges, and returns the earliest and latest date

I have a table of absence days, and I'm trying to build a view that will find all absence records for the same person which are consecutive, and returns the earliest consecutive date and latest consecutive date as well as the total days and hours -…
mtnutholme
  • 1
-3
votes
1 answer

How to find the max time difference in PostgreSQL that a value stayed on the same state?

I am working for a university project and this came up to me: I have a table like this: And I want to get the max duration that an actuator was on a state. For example, cool0 Was for 18 minutes. The result table should look like this: NAME …
Tomás Araujo
  • 23
  • 4
-3
votes
1 answer

Get minimum value in each island of non-NULL values

Photo of Sample Data in blue I have data in this format. I need to find missing for each set of non missing value. Or if I can create a columns that counts groups of non missing elements in SQL server. MOB ID Column1 Column2 0 123 Null …
StatsR
  • 35
  • 9
-3
votes
2 answers

Duration and idle time for a server from continuous dates

Server Customer. Start. End A. X. 12/10/2018 12:56 13/10/2018. 13:05 B. K. 12/10/2018 14:05. 12/10/2018. 14:25 A. N 12/10/2018.13:08. …
Shivam Shrivastava
  • 33
  • 1
  • 7
-3
votes
1 answer

Sum the differences group by another colomn

I have a table for a vehicle mileage in different states. Table: VehicleState VehicleID Mileage State DateTime 1 3000 TX 2016-09-20 03:00:00 1 3100 TX 2016-09-20 04:00:00 1 3200 …
Jaaaaaaay
  • 1,895
  • 2
  • 15
  • 24
-4
votes
2 answers

T-SQL Remove Duplicates from Groups BUT NOT GET TOP 1 FROM EACH GROUP

I do NOT want to get top 1 from each group! Pay attention to the explanation which I have provided at the last portion of my question! I have the following rows: | Code | Type | SubType | Date | |:----:|:----:|:-------:|:----------:| | 100…
Mohsen
  • 971
  • 2
  • 11
  • 29
-5
votes
2 answers

Counting consecutive blocks in SQL

I want to read a table with a SQL query in MySQL, which gives me the consecutive blocks of a row. The query may only last a maximum of 120 seconds, which I already exceed with my queries, I also can't manage to count consecutive numbers at all. So…
KarasuRou
  • 1
  • 3
1 2 3
…
91
92