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 can I delete trailing contiguous records in a partition with a particular value?

I'm using the latest version of SQL Server and have the following problem. Given the table below, the requirement, quite simply, is to delete "trailing" records in each _category partition that have _value = 0. Trailing in this context means, when…
bvy
  • 144
  • 2
  • 12
-1
votes
1 answer

Find max of total days using Partition By

Need SQL Query to get a max of the sum of Days where enddate is equal to startdate .Below is a…
Lucidity
  • 7
  • 3
-1
votes
1 answer

Splitting up Group By for similar values in a column within SQL Server

I am trying to split up the values in column5 so that when using a GROUP BY on column5 (seen below) the 2 value isn't all grouped together. Instead, the values will be separated out so that the first value of 2 is in it's own group the the second…
JJHawk
  • 11
-1
votes
1 answer

A follow up question on Gaps and Islands solution

This is continuation of my previous question A question again on cursors in SQL Server. To reiterate, I get values from a sensor as 0 (off) or 1(on) every 10 seconds. I need to log in another table the on times ie when the sensor value is 1. I will…
IoTian
  • 37
  • 5
-1
votes
2 answers

Get certain rows, plus rows before and after

Let's say I have the following data…
dbeavernoob
  • 1
  • 3
-1
votes
1 answer

Oracle SQL – counter for consecutive equivalent values under specific column

I'm trying to create a counter column for an SQL query, which will start with 1 and increase with each consecutive row with consecutive equivalent values, under a specific column, without re-ordering the query output. For example, for an SQL query…
golosovsky
  • 638
  • 1
  • 6
  • 19
-1
votes
1 answer

How to get the count rows for repeated non sequence values

I have a table with below values. Table Name: TestSeqeunce ----------------- ID | Sno | ----------------- 1 | 1 | 2 | 1 | 3 | 0 | 4 | 0 | 5 | 0 | 6 | 1 | 7 | 0 …
Dhamodaran M
  • 11
  • 1
-1
votes
2 answers

SQL How to get the first date from an uninterrupted list. Ie, if there is a gap, I want two first dates

Long time lurker, first time asking a question. If I make any etiquette mistakes, please correct me. Goal: Get the time an individual started and ended a position based on their job code and team code. So if someone left a position, then later…
SuperMF94
  • 1
-1
votes
1 answer

T-SQL create time windows (from/to) with alternating values

I need to transform rows on a monthly basis to rows with a time window based on alternating values. I have already tried various versions of partition, dense_rank and joins with row_number on row_number-1 but I somehow cannot get to the right…
Curti7
  • 1
  • 2
-1
votes
1 answer

can we solve basic transaction query using lead and lag window function in sql

I have this i/p table balance trs_date 27000 2020-01-01 27000 2020-01-02 27000 2020-01-03 31000 2020-01-04 31000 2020-01-05 27000 2020-01-06 27000 2020-01-07 32000 2020-01-08 31000 2020-01-09 i want this…
ashish kosare
  • 17
  • 4
-1
votes
1 answer

Count size of islands with no gaps

My data has 3 columns; ID, NAME, VALUE ID is sequential number like a rowcount There are many different NAMEs I show 3 in the data but there could be 10+ There are 2 VALUEs I would like to group the NAMEs and VALUEs in a 'local' manner and return…
pa beader
  • 9
  • 3
-1
votes
2 answers

How do I perform a data smear in SQL

So I have data that is like this: row EMPREF HOURS --------------------- 1 0023 NULL 2 0023 NULL 3 0023 NULL 4 0023 NULL 5 0023 20 6 0023 NULL 7 0023 NULL 8 0023 35 9 …
Nick Parker
  • 11
-1
votes
1 answer

Microsoft SQL Server 2016 - T-SQL puzzle - overlapping date ranges in segregated rows - 'Gaps & Islands' problem

I have asked 'Gaps and Islands' questions in the past, but this one is significantly different. I have an interesting question in Microsoft SQL Server 2016 database, T-SQL language. (Refer to image files and T-SQL scripts with temp tables) I have a…
user3812887
  • 439
  • 12
  • 33
-1
votes
2 answers

SQL query to get start and end date from a result set

I am working on one of requirement the raw data is in following format Requirement - Startdate should be the date when status changed to 1 and enddate should be the 1st date after the record status changed from 1 to any other…
BIDeveloper
  • 767
  • 3
  • 14
  • 28
-1
votes
4 answers

create time range with 2 columns date_time

The problem I am facing is how to find distinct time periods from multiple time periods with overlap in Teradata ANSI SQL. For example, the attached tables contain multiple overlapping time periods, how can I combine those time periods into 3 unique…
Yumeng Xu
  • 179
  • 1
  • 2
  • 11
1 2 3
…
91 92