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

How to pick the first and last from a given ordering when partitioning items into groups?

The real problem is with some huge product catalogs. In order to learn how to SQL this properly, I'm testing with this dataset. The jumbled up order of things is intended to help ensure that the logic will be sound. The following is a listing of the…
Steven Lu
  • 41,389
  • 58
  • 210
  • 364
-1
votes
1 answer

How can I solve gaps and islands problem in mysql for gaps with zero values and islands with non-zero values

I read a lot about MySQL gaps and islands problem, but I cannot find anything enough closer to understand my problem. I have gaps from zeros and islands from 15. You can see what I am talking about in the following tables The first table is my…
Даяна Димитрова
  • 93
  • 10
-1
votes
1 answer

SQL join against date ranges

I need to find the difference between the date. Sample Data in the table CREATE TABLE #TEMP ( StartDate DATE, EndDate DATE ) INSERT INTO…
Siva Dss
  • 9
  • 1
-1
votes
2 answers

Microsoft SQL Server 2016 - T-SQL language - 'Gaps & Islands' problem - tricky partition

I have 5 employees with ID values : 111, 222, 333, 444, 555. I have a source dataset that has data for all five of them on all calendar days between Mar 28, 2021 and Apr 5, 2021. (This is just a small sample subset of my actual data). You can use…
user3812887
  • 439
  • 12
  • 33
-1
votes
3 answers

Find the Count of Consecutive Occurrence in Table using SQL

enter code here have a table like this in my SQl Server NTN | Channel Date_Time 006175A | Yellow 15-9-2021 10:16:00:00 0061751 | Green 15-9-2021 10:15:00:00 00617523 | Red 15-9-2021…
Umair Hassan
  • 1
  • 2
-1
votes
1 answer

select tuples with more than 30 seconds from each other

sorry for my bad formating, I've never used this platform. I have a table with some timestamps and I want to select the ones which have more than 30 seconds from each other and are consecutive. It goes like this: table called 'nova' with only one…
Lucas Lago de Lima
  • 1
  • 1
-1
votes
2 answers

Count the number of consecutive Zeros along with their instances SQL

Data is as follows: Row_No ID Bal 1 01 0 2 01 0 3 01 0 4 01 10 5 01 0 6 01 0 7 01 20 8 01 0 9 02 30 10 02 0 11 02 …
Asma Damani
  • 197
  • 3
  • 11
-1
votes
1 answer

Count the number of Consecutive 0s in a column with SQL

I have a table that contains following data: sample data It has balance containing 0s and 1s. Required is the total number of consecutive 0s as well the count of consecutive instances, as below. output
Shrewd Henry
  • 1
  • 2
-1
votes
1 answer

Using gap and island knowledge to find longest time without change in employment (SQL)

The question I am dealing with: Wwhat is the longest period of time where there was no change in employment at the company. A change in employment is someone new starting or an existing employee leaving the company. The table contains employee id,…
Jeffrey Zhu
  • 1
  • 1
-1
votes
1 answer

Display 1,2,3,4 as 1-4 in oracle pl/sql

I have a requirement as- if the user selects checkboxes containing 1,2,3,4 of type 1 and 2,3 of type2 then I should display as (1-4)type1 , (2-3)type2 as the output. We have to do it in backend. I have used LISTAGG but couldn't achieve the desired…
Shravya M
  • 25
  • 1
  • 6
-1
votes
1 answer

Group sequence value in column mysql

Table mytbl have two columns: col_1 & col_2. I wanted to group range of values in col_1 for single col_2 value. Example: col_1 col_2 1 3 2 1 3 3 4 3 5 2 7 3 8 3 9 3 10 1 I wanted to filter out the a range for col_2 =…
Zen
  • 21
  • 7
-1
votes
1 answer

RANK in SQL but start at 1 again when number is greater than

I need an sql code for the below. I want it to RANK however if DSLR >= 60 then I want the rank to start again like below. Thanks
Zipping2010
  • 15
  • 5
-1
votes
1 answer

SQL Count Unacknowledged Alarms

I'm looking to count the number of alarms that became active in a time range but were not acknowledged and the number of alarms that were not cleared. There are three types of events that are all stored in the same table: alarm activation,…
Benjamin Sperl
  • 21
  • 5
-1
votes
1 answer

Group data in intervals without 0

I have got following table: +---------------------+--------+----------+ | MeasureInterval | Car_id | Distance | +---------------------+--------+----------+ | 2020-12-15 17:00:00 | 1 | 20 | +---------------------+--------+----------+ |…
Petrik
  • 823
  • 2
  • 12
  • 25
-1
votes
2 answers

row number in sql -keep it same for change in value

I have a SQL questions: I want to populate rank for each record as a next up number. However I want to keep the rank same for records for which the adjust values are not same. Sample data : # | value | date1 | * rank…
Vijayasarathi Balasubramanian
  • 55
  • 12
1 2 3
…
91 92