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

Aggregate Rows Start and Finish Date < 10 mins

I would like to do the following transformation in SQL Server database. Aggregate over Tag_of_thing & Status_of_thing such that... Note in this case end shift is the row above, see example tables If Start_shift_date_time = End_shift_date_time then…
Shella
  • 89
  • 1
  • 7
-1
votes
1 answer

Find sequence number in SQL Server

This is my table in SQL Server : number grade weight -------------------------- 1 1 185 2 1 179 3 3 191 4 3 192 5 3 193 6 1 194 7 …
MHSHG
  • 45
  • 4
-1
votes
1 answer

Update another row value in SQL Server

I have a table with name, location, startdate and enddate as follows: +------+----------+-----------+-----------+-----------+ | name | location | startdate | endate | is_active | +------+----------+-----------+-----------+-----------+ | A |…
Shimma
  • 7
  • 3
-1
votes
2 answers

How do I query how many items have changed the status between datetime?

I have a history table like this id, name, is_active, mod_date 1, 'name1', 0, 2020-06-09 21:00:00 1, 'name1', 1, 2020-06-09 22:00:00 2, 'name2', 1, 2020-06-09 20:00:00 2, 'name2', 0, 2020-06-09 20:10:00 2, 'name3', 1, 2020-06-09 20:20:00 3, 'name4',…
toy
  • 11,711
  • 24
  • 93
  • 176
-1
votes
2 answers

Count largest negative series in table

I am looking for a series of negative/positive numbers. Let's say that I have a table with two columns: order_time and win, where order_time is a date and win is +1 or -1. I would like to find the greatest series of negative values in column "win".…
ltrd
  • 326
  • 1
  • 8
-1
votes
2 answers

PostgreSQL query: write a query to return the maximum of each group of consecutive numbers

Given a set of numbers like "1,2,3,6,7,8,11,12,15,18,19,20", write a query to return the maximum of each group of consecutive numbers. need to first find which numbers should be in a group, and then find the max in each group. create table tt (c1…
JackDaniels95
  • 35
  • 4
-1
votes
1 answer

postgresql given "1,2,3,6,7,8,11,12,15,18,19,20", a query to return the maximum of each group of consecutive numbers

given 1,2,3,6,7,8,11,12,15,18,19,20, write a query to return the maximum of each group of the consecutive numbers are grouped by the query below, but I don't know how to obtain the maximum for each group of consecutive numbers with my current…
gigi
  • 23
  • 5
-1
votes
1 answer

Remove Time Overlaps in Sessions Records

I have a Sessions table with the columns (User_ID, Sessions_ID, LogOn, LogOut), the user can open more than one session in the same time, my goal is to calculate the pure time spent on my system for each user. I used the following query: SELECT …
A.Kallas
  • 75
  • 1
  • 10
-1
votes
1 answer

SQL query data that appears for consecutive years

I have tried several times to figure this out but no luck. I have one table that I am trying to query. InvNo (primary key), CustID, InvAmt, DatePD I want to pull all of the customers that have paid at least one invoice for 3 consecutive fiscal…
Bill Dickson
  • 13
  • 4
-1
votes
3 answers

SQL Query to get count of cycles if it matches value of column in consecutive rows

I have table named vehicledata which consists 3 columns: id, Veh No, and Veh Mode. My data looks like this: id Veh No Veh Mode 1 KA03-003 IDLE 2 KA03-003 IDLE 3 KA03-003 IDLE 4 KA03-003 DRIVE 5 KA03-003 DRIVE 6 KA03-003 …
Pradeep Gn
  • 13
  • 4
-1
votes
1 answer

Dense rank for duplicated values

id val dense_rank 1 11 => 1 2 11 1 3 22 2 4 33 3 5 33 3 6 11 4 What sql should like for getting this kind of dense_rank?
Temirlan Kabylbekov
  • 13
  • 5
-1
votes
2 answers

SQL Group by date with multiple same results

How to write a statement that groups data by date but shows when data changed for the first time date - when data changed a,b,c - some data and it can be…
SEQW
  • 9
  • 2
-1
votes
1 answer

Postgres: Aggregate rows based on flag change

Hey folks maybe somebody has a clue on that. I have a table in a format like this: id timestamp status value 82240589 2020-03-01 09:13:46 70 22.00 82240589 2020-03-01 09:13:57 70 34.00 82240589 2020-03-01…
Michael Wittmann
  • 3
  • 1
-1
votes
1 answer

calculate login logout time in mysql

I have a table structure like this Time capture in a single column on basis of the event. But I required one complete event in a single row. Required data like this:
Shyam Maurya
  • 3
  • 2
-1
votes
1 answer

How to group adjacent row and sum the data in SQL

I would like to sum the Value and group the adjacent row in SQL as shown below. May I know how to do that? My code now: Select ID, Value from Table_1 Further question how about this?
simpson
  • 3
  • 3
1 2 3
…
91 92