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

SQL return empty records for every storage and every day

I tried to list sales for each warehouse even when there were no sales on a given day based on events in the tables. Unfortunately, I am not able to achieve the intended result, when there are no records for a particular warehouse on a given day it…
CheeseDoodle
  • 1
  • 1
-1
votes
1 answer

Redshift Postgresql Start row_number after a condition is met

I'm trying to start a row number partitioned by cashaccountid, ordered by cashentrycreatedat that starts when a particular condition is met. In this image, we only have one cashaccountid (to make things easier) and it's sorted by…
Denisse
  • 27
  • 2
-1
votes
1 answer

Numbering long chains of repeating values in a column of a table in PostgreSQL using window functions

In PostgreSQL, I have a table with a column that has long repeats of 0's and 1's. I would like to create another column that ranks these with repeating ranks. Here is an example of what I'd like to see: Column New…
user4562262
  • 95
  • 1
  • 9
-1
votes
2 answers

Get the maximum consecutive count of a name in PostgreSQL

I was asked this question in a job interview: There is a table with vehicle names mentioned in a column. Output when we check for name=car we must get as 4 i.e the maximum count of continuous occurrence of 'car' in the column. Can somebody help me…
Manoj Pr
  • 3
  • 2
-1
votes
1 answer

Attempt number grouped by users in SQL or Python

I have a table similar to this (without the attempt…
nleffell
  • 1
  • 1
-1
votes
1 answer

sql to assign an incrementing number based on another column value

I have a sql problem that I would like some assistance with. Please see the image below: So, it is the last column (newflag) that I want implemented in a sql. The idea is to increment by 1 when the value in 'Breakpoint' changes. Starting value is…
JGF
  • 19
  • 3
-1
votes
1 answer

Oracle Function to extract first / last value of code that with multiple series

I'm having difficulties finding a way to extract the first and last values for a code in a series that can occur multiple times in a day. Example, table data agents schedule can have breaks, lunch and other codes such as coach and ect. I need to…
user20459917
  • 35
  • 5
-1
votes
2 answers

How to summarize days by a flag field?

How can I summarize the days Kyle has on this table using a flag field? Row Name Flag Days 1 Kyle 0 12 2 Kyle 0 02 3 Kyle 1 01 4 Kyle 1 18 5 Kyle 1 01 6 Kyle 0 01 7 Kyle 1 01 8 Kyle 1 01 I want this…
Guilherme
  • 1
  • 1
-1
votes
2 answers

Postgres Sql syntax error at end of input

create table hall_events ( hall_id integer, start_date date, end_date date ); delete from hall_events; insert into hall_events values…
Abinaya Muruganandhan
  • 1
-1
votes
2 answers

I wanna get query result Group By and Orderd by Date

Can I get result like below with Oracle Query? Group by Type but seperated by date order. Date can't be overlaped. [RAW DATA] TYPE FR_DATE TO_DATE INCOME A 04/14 01:04:15 04/14 22:10:15 100 A 04/15…
user3085427
  • 29
  • 4
-1
votes
2 answers

Find consecutive null values started date on date column and bit datatype column

create table history(response_date date, r_value bit); insert into…
S Nagendra
  • 33
  • 4
-1
votes
2 answers

SQL select min and max of a date variable with criteria

I'm using Teradata SQL Assistant for an analysis on date variables. Basically I have individuals that receive a treatment on a given date. These individuals might receive the treatment several times a year across several years but when there is a…
user5553440
  • 1
  • 2
-1
votes
1 answer

How to count the number of days in Mysql where an event has happened in a row consecutively daily?

See table dataHow to count the number of days in MYsql where an event has happened in a row consecutively daily? CREATE TABLE MrDataConverter ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, num INT, controller_id INT, event_type INT, date…
Ligia C
  • 3
  • 2
-1
votes
2 answers

Current and previous days date diff in days with some condition

I have the first three fields of the following table. I want to compute the number of consecutive days an amount was higher than 0 ("days"…
sbtota
  • 3
  • 2
-1
votes
2 answers

Sum over a given time period

The following codes gives the total duration that a light has been switched on. CREATE TABLE switch_times ( id SERIAL PRIMARY KEY, is1 BOOLEAN, id_dec INTEGER, label TEXT, ts TIMESTAMP WITH TIME ZONE default…
rm.
  • 509
  • 1
  • 6
  • 8
1 2 3
…
91 92