1,485 questions
5
votes
1
answer
139
views
Find the Active Item for a Given Date With Time Intervals and Item Criteria
I have a problem that's similar to this one: Merge overlapping time intervals based on priority and type - SQL Server
But it's not quite the same.
I have items that have a type, a weight and an active/...
- 229
1
vote
2
answers
154
views
How can I count the occurrences of a value in between two specific values?
I have a table in SQL that shows different rule firings with timestamp ordered from oldest to newest - like this:
Timestamp
Customer ID
Rule Name
2025-07-21 11:21:20
customer 1
quick_hit_rule
2025-07-...
- 63
2
votes
3
answers
136
views
Changing data structure from "Date" to "From_Date" and "To_Date" based on ID and Flag
I have IDs, their as at date (end of month format), and their corresponding flags. I only care if the flag is Y.
It is in the following format:
ID
as_at_date
Enabled_Flag
1
31/01/2025
Y
1
28/02/2025
Y
...
- 23
1
vote
3
answers
112
views
Create Iterative Groups in SQL where certain thresholds start a new group number
So if I have a dataset where you have people and days they were in, I want to create groups (by person) for when they were close together. But if there is a big break, I want to start a new group ...
- 1,164
1
vote
5
answers
136
views
How to count the days between consecutive days across multiple rows
I want to count days between consecutive dates.
For example, if I have 3 consecutive dates 01-MAY-25, 02-MAY-25 and 03-MAY-25 in 3 rows, then I want 1 row with the begin date 01-MAY-25 and number of ...
- 742
1
vote
1
answer
79
views
Gaps and Islands Getting Last Island Window
I have a table with status changes that I want to chunk into islands and eventually get my most current island based on when my status changed. I'm using this query to retrieve the current, previous ...
- 512
1
vote
2
answers
127
views
SUM unique GROUP BY values in sqlite and count groups of consecutive values
I'm quite a novice sql user and I'm struggling with a sqlite query over a GROUP BY. Here's a simplified example of my problem using this table data:
id, pid, tid, duration
1, 1, 12, 0.099999
2, 1, 13, ...
- 676
0
votes
1
answer
77
views
Concat the first and last sequential line number with a hyphen
Our table stores the line numbers of an order. Is it possible to concat them into a string, where the first and last sequential lines are separated by a hyphen, and non-sequential lines are separated ...
- 143
1
vote
2
answers
118
views
Detect 1st row of an ordinal chain using recursive CTE or alternative
I have the following data:
UserId
SubId
EduId
SemOrd
1
701
500
10
1
702
255
11
1
703
500
12
1
704
500
14
1
705
500
15
1
706
500
16
1
707
500
17
2
...
...
..
Given a UserId and SubId, I want to find ...
- 2,187
0
votes
1
answer
76
views
Query that groups rows based on the difference between datetime column value from a previous row?
I have a strage case of grouping query.
I need to write a sql server query that groups rows based on the difference between datetime column value from a previous row.
That's the query:
select
[id],
...
- 3
1
vote
1
answer
62
views
MySQL filtered gaps and islands: avoiding temporaries and filesorts?
CREATE TABLE `messages` (
`ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`Arrival` TIMESTAMP NOT NULL,
`SenderID` INT UNSIGNED NOT NULL,
-- Fields describing messages skipped
PRIMARY ...
1
vote
2
answers
113
views
Postgres Fixed 7-day window shifting data
I am trying to write a query to generate fixed non-overlapping windows and counting the number of records within each window. The next window starts on the next date of record after the previous ...
- 58
4
votes
2
answers
295
views
SQL Gaps and Islands with multiple overlapping criteria
My (anonymized here) data has several completely separate sidewalks with various lines of chalk in different colors along each sidewalk. I need to find the start and end of each section of drawing ...
- 1,131
0
votes
0
answers
55
views
How to convert sequence or non-sequence DATE in Rows to FROM Date and TO Date Range (in Oracle SQL) [duplicate]
Whenever there a gap between dates, it should consider same date as from and to other wise it should show min date in from and max date in to column, My data is like
HolidayDate
-----------
21-JAN-...
1
vote
1
answer
88
views
How to count rows between two IDs in postgreSQL without grouping by
I am working with a dataset that tracks folder movements within conversations. I have a folder_movement table that contains conversation_id, updated_at, and movement details.
I created a folder_cum ...
- 11
0
votes
2
answers
74
views
Calculate Date Difference for Non-Consecutive Months
I am trying to find gaps in enrollment and have a table set up like this:
ID
Enrollment _Month
Consecutive_Months
1
202403
1
1
202404
2
1
202405
3
1
202409
1
1
202410
2
1
202411
3
2
202401
1
2
202402
...
- 89
0
votes
3
answers
201
views
Using SQL Server window functions with year and month(Period of time)
Please consider this script:
Declare @tbl Table
(
F1 int,
F2 int,
Year int,
Month tinyint
)
Insert into @tbl
values
(10, 1, 2020, 1),
(10, 1, 2020, 2),
(10, 1, 2020, 3),
(10, ...
- 13.1k
-5
votes
2
answers
122
views
How to check records that come one after another in MySQL
I have a table movie that has id, movie name, and status columns.
id
name
status
1
movie1
0
2
movie1
1
3
movie1
0
4
movie1
1
5
movie1
0
6
movie1
0
7
movie1
1
8
movie1
0
9
movie1
1
10
movie1
1
11
...
0
votes
1
answer
68
views
Copy an ID to rows with adjacent timestamp ranges sharing a class
Here is a sample table.
create table test(ID,Start_date_time,End_date_time,class)
as values
(131, '5/26/2021 11:42', '5/26/2021 12:42', 'AAA')
,(132, '5/26/2021 12:42', '5/26/2021 13:18', 'AAA')...
- 177
1
vote
1
answer
75
views
Sum a Column of a Timeseries by an Order Number when the Ordernumber is not unique
I have a table like this: demo at db<>fiddle
CREATE TABLE test(id, order_id, start, end1, count) AS VALUES
(1, 1, '2023-12-19 10:00:00'::timestamp, '2023-12-19 11:00:00'::timestamp, 15),
(2, 1, '...
- 31
1
vote
2
answers
146
views
Roll Up with column changes [closed]
Table below - order by [low],[high]
Group
Section
Low
High
A
01
01
10
A
01
11
15
A
01
18
18
A
01
19
25
A
02
26
30
A
02
31
32
A
01
33
40
A
01
41
41
By using the table above, I would like to create ...
- 13
1
vote
5
answers
136
views
Streak for a given endDate SQL (Postgres)
Input data
date
number
2024-11-02
1000
2024-11-03
500
2024-11-05
1000
2024-11-06
1000
2024-11-07
1000
2024-11-08
500
2024-11-14
1000
2024-11-15
1000
for a given date I want to get the streak (dates ...
- 145
1
vote
4
answers
117
views
Number of missing periods between dates
I'm using Postgres and I would like to find missing ranges of dates. I've got this table with these data :
create table event_dates(date)AS VALUES('2024-12-09'::date)
...
- 13
2
votes
1
answer
82
views
How to calculate average stock status in day
Stock status for days is in table
create table stockstatus (
stockdate date not null, -- date of stock status
product character(60) not null, -- product id
status int not null, -- stock status in ...
- 28.2k
2
votes
1
answer
70
views
How to GROUP_CONCAT row values, but break groups by an arbitrary "gap" flag?
Let's say I have an existing SQL table (which I am given, I have no control over it) describing a list of connections between cities, with a column flagging any gaps. Here's a simplified example:
...
- 353
0
votes
1
answer
104
views
SQL query to audit employee claim based on specific sequence of rows defined by criteria
I need to create an exception/audit report for our accounting section.
The business case is an employee is deployed to remote location and can claim lodging cost if they are away from their family. If ...
- 119
0
votes
1
answer
121
views
Select rows without overlap / dynamic 1 hour intervals
time_start
time_end
'2024-01-01 12:30'
'2024-01-01 13:30'
'2024-01-01 12:40'
'2024-01-01 13:40'
'2024-01-01 12:45'
'2024-01-01 13:45'
'2024-01-01 13:36'
'2024-01-01 14:36'
'2024-01-01 13:50'
'2024-01-...
- 23
1
vote
2
answers
78
views
Windowing Function To Keep Track Of "Latest Seen" Row
I have the following test data:
Id
Special
1
0
2
0
3
1
4
0
5
0
6
0
7
1
8
0
9
0
And I want to compute this:
Id
Special
LastSeenSpecial
1
0
NULL
2
0
NULL
3
1
3
4
0
3
5
0
3
6
0
3
7
1
7
8
0
7
9
0
7
from ...
- 47
0
votes
2
answers
67
views
Overlapping and Non overlappping SQL query
We have below schema:
CREATE TABLE enrollments (
case_id INT,
enrollment_id VARCHAR(10),
open_date DATE,
closed_date DATE,
status VARCHAR(10)
);
Let's assume the records are:
...
1
vote
1
answer
150
views
SQL cumulative sum of dates and calculate the number of sessions in time duration
I am stuck with a SQL to reset a cumulative sum of time after reaching duration of 20 minutes and calculate the difference between date time column and reset time. I'm not sure if it's possible and ...
- 69
-1
votes
2
answers
100
views
Using islands and gaps to find slots that leave other staff slots open
We need to build a scheduling mechanism multiple several reps in a store that make phone calls. However, at least one rep must remain available at all times so that if there is a walk in they can give ...
- 873
1
vote
1
answer
115
views
Algorithms to fill in islands within lakes Python
I have an ndarray where each cell is either land (1) or water (0). In this ndarray, the background and border is all land (1), and there are lakes within. Some of these lakes have islands within them.
...
3
votes
5
answers
566
views
PostgreSQL (AWS Redshift) Forward-fill null values based on previous non-null value in the same column
I have the following dataset:
create schema m;
create table m.parent_child_lvl_1(customer_id,date,order_type,order_id,sub_id)
as values
(108384372,'18/09/2023'::date,'sub_parent_first_order',...
- 132
0
votes
2
answers
103
views
Find a gap > 10min and select begin/end dates in timestamp logs
Lets say I have a table with logs. Two columns: one with user name and second with record timestamp.
Records appear in table every 10 minutes for each user IF this user was active.
user
timestamp
...
- 3
1
vote
2
answers
296
views
How to create an SCD2 type table from partitioned table PostgreSQL/Clickhouse?
I have a table partitioned by date-type column ds, with many fields.
Not all columns change every day, therefore most rows are just duplicates of the previous rows.
I want to create an SCD2 table from ...
3
votes
2
answers
176
views
Insert missing rows in time series consumption history
I'm looking for assistance with writing a PostgreSQL query in Apache Superset.
I have metered consumption data, where the meters have unique IDs. Normally, a meter is read at the start of every hour, ...
0
votes
1
answer
74
views
Identifying Overlapping Appointments with One Non-Overbooked Occurrence
Scenario:
Appointments are made with start and end times. Appointments are allowed to overlap, but for reporting purposes, we need to know when there are overlapping appointments (called overbooking) ...
- 13
-1
votes
1
answer
80
views
Get ID ranges for multiple sections
How do I get the lower- and upper-bound range(s) of the following data for each set of "1s":
LogFileTextID
ErrorLineFlag
88182351
0
88182352
0
88182353
0
88182354
0
88182355
1
88182356
1
...
- 31
5
votes
3
answers
163
views
Perl: makes array of item IDs shortened
I have a large set of numbers, basically 1001 .. 150000 for a database using MySQL
There are a ton of gaps in the IDs on the database, so not all IDs exist. It can go from 100000 - 10500, then the ...
- 53
0
votes
2
answers
85
views
The problem of extracting consecutive dates in SQL query
There is a table of customers with purchases made in a certain month. It happens that a customer did not buy anything in a certain month, so this month is skipped.
Task:
I need to extract only those ...
3
votes
3
answers
125
views
Finding Product Switches on Account Between Dates
I am working on a problem were we have a table that holds id, product code , accountid , date
CREATE TABLE AccountBalanceAndProduct
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[EffectiveDate] [date] ...
- 233
1
vote
1
answer
72
views
SQL Grouping based on one field and a flag
I struggle to get an sql that groups my records based on the following input data
AB
BIS
MyHash
flag
2024-01-01
2024-01-15
111
0
2024-01-16
2024-03-01
111
0
2024-03-02
2024-03-31
222
0
2024-04-01
2024-...
- 13
1
vote
2
answers
183
views
How do I a gap and island analysis with a power query?
I have a dataset similar to the one below, and I am trying to do a gap and island analysis on it.
Item
TransDate
InventoryCount
explanation
Bicycle
01/01/2023
0
First time the item Bicycle has been ...
- 23
2
votes
3
answers
128
views
How to performantly self-join same table multiple times with ascending and descending sort order?
Data Definition
I have a status table like so, attributes not relevant to the question are omitted:
id
created
value
1
2024-06-24T13:01:00
error
2
2024-06-24T13:02:00
ok
3
2024-06-24T13:03:00
warning
...
- 666
0
votes
1
answer
78
views
How can I find the longest number of consecutive nulls in a column in MySQL?
Supposing I have a table consisting of something like:
date | A | B | C |
02/01/2020 | 3 | 5 | null |
03/01/2020 | 8 | 3 | null |
04/01/2020 | 3 | 4 | null |
05/01/2020 | 5 | 9 | 3 |
06/01/...
- 3
0
votes
1
answer
131
views
Calculate streaks at scale
So I need to calculate the streaks of a user (the number of consecutive days he created an entry). For the moment, I made a query that groups entries and it gives me the last group of streaks. Here is ...
- 47
0
votes
1
answer
57
views
Overall Order from a Reduced Dataset Against a Large Dataset
There is a scheme used in a process to create a delta of changes in a dataset from one day to the next. The query needs to get the overall order of a record within the entire result set of a view, ...
- 15.2k
1
vote
3
answers
127
views
Compute column value based on previous row recursively
Given the following scheme and values I want to compute the value of each row starting by adding 1 to the previous row value. If the row value is null then set null. If there is a value then add 1 ...
- 807
1
vote
1
answer
87
views
Postgres search missing date in hourly sequence
I have a table period_of_hours in PostgreSQL. This table contains several column but for my case is important column timestamp (timestamp without time zone) and key(character varying).
I have a ...
- 157
0
votes
3
answers
114
views
How to find gaps in an int column (grouped by another column) in MySQL?
There are numerous SQL questions and answers about finding gaps in sequences, but I could not find any that have the wrinkle my situation has: I need to group the sequence by another column.
My table (...
- 33.6k