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

How to check consecutive values in rows in TSQL?

I have the following table: And I am looking for an output like this with a consecutive available two slots. Preferably the consecutive number should be a variable like @n = 2. How to achieve this in TSQL? Thanks! Update 8/3/2022: I undeleted…
WinFXGuy
  • 1,527
  • 7
  • 26
  • 47
-2
votes
3 answers

Get maximum of sequence

+----+-------+ | id | value | +----+-------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 5 | D | | 6 | D | | 7 | N | | 8 | P | | 9 | P | +----+-------+ Desired…
lozin
  • 1
  • 2
-2
votes
1 answer

SQL Count Consecutive Days per line

Looking to get the "Total Consecutive Days" per row. Shift Table Contains ShiftId, ClientID, ServiceId and ProviderID ShiftDetails Table has the dates. There can be multiple Shifts with the same ShiftId, ClientID, ServiceId There can even be…
monsey11
  • 243
  • 4
  • 18
-2
votes
1 answer

SQL Calculate Consecutive hours

Need to calculate consecutive hours. Here is the data ╔════╦══════════╦════════════╦═══════════╦═══════╗ ║ ID ║ ClientID ║ Date ║ From(Min) ║ To ║ ╠════╬══════════╬════════════╬═══════════╬═══════╣ ║ 101║ 2563 ║ 2020-06-19 ║ 360 …
monsey11
  • 243
  • 4
  • 18
-2
votes
2 answers

Get and filter dates SQL

I have this table without pk | Peo | Pos | DATE_BEGIN | DATE_END | XXXX | +------+-----+------------+------------+------+ | 9302 | 8 | 2017-10-02 | 2017-12-31 | NULL | | 9302 | 8 | 2018-01-01 | 2018-01-01 | NULL | | 9302 | 8 | 2018-01-02 |…
Luis
  • 25
  • 4
-2
votes
1 answer

SQL or LINQ: how do I select records where only one paramater changes?

Say we have this list: Id IdRef myColumn anotherColumn 448 70 1 228 449 70 1 2s8 451 70 1 228 455 70 2 2a8 456 70 2 s28 457 70 2 …
user441365
  • 3,934
  • 11
  • 43
  • 62
-2
votes
3 answers

SQL group by changing column

Suppose I have a table sorted by date as so: +-------------+--------+ | DATE | VALUE | +-------------+--------+ | 01-09-2020 | 5 | | 01-15-2020 | 5 | | 01-17-2020 | 5 | | 02-03-2020 | 8 | | 02-13-2020 | 8 | | …
Geoffrey Saunders
  • 135
  • 2
  • 9
-2
votes
1 answer

Add unique id to groups of ordered transactions

I currently have a table with transactions that are sequentially ordered for each group like so: | transaction_no | value | |----------------|-------| | 1 | 8 | | 2 | 343 | | 3 | 28 | | 4…
BenLevi
  • 7
  • 3
-2
votes
2 answers

Aggregate only Sequential values

I have a table with 3 columns Create table test ( Created Datetime , Flag Bit , Amount Money ) that looks like this Created Flag Amount 2019-12-01 00:00:00.000 1 50,40 2019-11-21…
PoNti3
  • 41
  • 1
  • 1
  • 7
-2
votes
2 answers

Aggregate numbers that are in sequence

I have a table part_tab with column serial_no: PART_NO SERIAL_NO A 1 A 2 A 3 A 5 A 7 A 8 A 9 A 10 I would like to aggregate…
slayer22
  • 63
  • 6
-2
votes
1 answer

GROUP BY adjacent records

How to group a column by adjacent records in SQLite? Situation MCVE for a 12 table JOIN-ed SELECT query (grouped by multiple columns). Table Table entity_log storing value (over time; timest as Unix time timestamp): CREATE TABLE entity_log ( id …
user4157124
  • 2,809
  • 13
  • 27
  • 42
-2
votes
3 answers

Given a sequence of numbers how to identify the missing numbers

I would like to get all missing numbers in a sequence of numbers. Just wondering if there is a better approach than below? SELECT x FROM ( SELECT x, LAG(x,1) OVER ( ORDER BY x ) prev_x FROM ( SELECT * FROM (…
Teja
  • 13,214
  • 36
  • 93
  • 155
-2
votes
2 answers

Merge Dates into continous date

|Resource ID | Start Date | End Date| ------------------------------------- |24565865 | 04-01-16 | 29-01-16| |24565865 | 29-01-16 | 01-02-16| |24565865 | 01-02-16 | 25-03-16| |24565865 | 25-03-16 | 01-04-16| |24565865 |…
Akash
  • 77
  • 10
-3
votes
1 answer

SQL Server 2022: Latest Update CU6 - Why does Last_Value does not work when there is no Partition?

Can anyone explain why Last_Value does not take the last value (which is last as per order by) for all rows ? select * from mytableone Initial Data (Please click on link) Query Used: select t.*, LAST_VALUE(PAST_DUE_COL) OVER (Order by ID) as…
Aswin K
  • 1
  • 1
-3
votes
1 answer

Java merge overlapping date intervals

Usually this type of algorithm is done using SQL (gaps and islands) but I need to find a way to do it in Java. I have a Set of objects : Set The UnavailableBlock class is as follows…
wazowski
  • 115
  • 8
1 2 3
…
91
92