Advertisements Below is my table if exists(select top 1 1 from sys.tables where name=’ObjInfo’) drop table ObjInfo create table ObjInfo(id int identity ,ObjNumber int ,ObjDate datetime ,ObjConditionId int) insert into ObjInfo(ObjNumber,ObjDate,ObjConditionId) values(1,’2014-01-03′,1) ,(1,’2014-01-05′,1) ,(1,’2014-01-06′,1) ,(1,’2014-01-08′,2) ,(1,’2014-01-13′,1) ,(1,’2014-01-15′,1) ,(1,’2014-01-25′,4) ,(2,’2014-01-01′,1) ,(2,’2014-01-05′,1) ,(2,’2014-01-07′,2) ,(2,’2014-01-08′,2) ,(2,’2014-01-12′,2) ,(2,’2014-01-14′,3) ,(2,’2014-01-15′,4) My Job is to display ObjectNumbers wise when condition was… Read More Need split the data when condition is getting changed without loop

Advertisements I have the following table: CREATE TABLE trajectory( user_id int, session_id int, lat double precision, lon double precision ); INSERT INTO trajectory(user_id, session_id, lat, lon) VALUES (1, 25304,39.984702, 116.318417), (1, 25304, 39.984683, 116.31845), (1, 25304, 39.984686, 116.318417), (1, 25304, 39.984688, 116.318385), (1, 20959,40.008304, 116.319876), (1, 20959,40.008413, 116.319962), (1, 20959,40.007171, 116.319458), (2, 55305, 39.984094, 116.319236),… Read More Add a column and populate based on other columns values

Advertisements I have a table: user_id type date 1 prime 2022-05-31 1 usual 2022-06-30 1 usual 2022-07-31 1 usual 2022-09-31 I need to get a table grouped by user_id and type and validity period. The main difficulty is that the periods may not be consecutive (they have more than 1 month distance between each other):… Read More Gaps in consecutive periods

Advertisements I hope you are doing well! Sorry for the basic question, however, I have been struggling to get the rows of the longest consecutive same value of the whole table and I was wondering what the query would be to achieve this? Example, let’s say that I have the table Learning: rowID values 1… Read More Get the Rows of the Longest Consecutive Same Value of the Whole Table

Advertisements I have these tables: CREATE TABLE labels(user_id INT, session_id INT, start_time TIMESTAMP,mode TEXT); INSERT INTO labels (user_id,session_id,start_time,mode) VALUES (48,652,’2016-04-01 00:47:00+01′,’foot’), (9,656,’2016-04-01 00:03:39+01′,’car’),(9,657,’2016-04-01 00:26:51+01′,’car’), (9,658,’2016-04-01 00:45:19+01′,’car’),(46,663,’2016-04-01 00:13:12+01′,’car’); CREATE TABLE raw_data(user_id INT, session_id INT,timestamp TIMESTAMP) ; INSERT INTO raw_data(user_id, session_id,timestamp) VALUES (8,652,’2016-04-01 00:46:11.638+01′),(8,652,’2016-04-01 00:47:00.566+01′), (8,652,’2016-04-01 00:48:06.383+01′),(9,656,’2016-04-01 00:14:17.707+01′), (9,656,’2016-04-01 00:15:18.664+01′),(9,656,’2016-04-01 00:16:19.687+01′), (9,656,’2016-04-01 00:24:20.691+01′),(9,656,’2016-04-01 00:25:23.681+01′), (9,657,’2016-04-01 00:24:50.842+01′),(9,657,’2016-04-01 00:26:51.096+01′),… Read More Gap-and-island for more than time threshold

Advertisements I have an Oracle table with time stamps and I need to check on all rows where the current row is bigger the the previous row by less than a minute and state the start and end time and if its bigger than a minute I need to start a new group as in… Read More grouping rows with less than one minute separating row from previous row in Oracle

Advertisements With a given dataset: WITH ranges AS ( select to_date(‘01.01.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_from, to_date(‘31.03.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_to from dual union select to_date(‘27.03.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_from, to_date(‘27.04.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_to from dual union select to_date(‘01.05.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_from, to_date(‘31.12.2021 00:00:00′,’DD.MM.YYYY hh24:mi:ss’) date_to from dual ) SELECT * FROM ranges; How to find the gap 28.04.2021-30.04.2021.?… Read More How to fill date range gaps Oracle SQL