Combine rows when the end time of one is the start time of another (Oracle)
Maybe this? (I don't have a SQL machine to run it on)
WITH
sequenced_data AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY name ORDER BY start_inst) NameSequenceID,
ROW_NUMBER() OVER (PARTITION BY name, code, subcode ORDER BY start_inst) NameStateSequenceID,
*
FROM
data
)
SELECT
name,
MIN(start_inst) start_inst,
MAX(end_inst) end_inst,
code,
subcode
FROM
sequenced_data
GROUP BY
name,
code,
subcode,
NameSequenceID - NameStateSequenceID
Combine rows when the end time of one is the start time of another
Try this(>= SQL Server 2005):
WITH qry AS
(
SELECT a.*
,ROW_NUMBER() OVER (ORDER BY [start]) rn
FROM (SELECT Date as 'start', DATEADD(s,Seconds,Date) as 'end', State, Seconds FROM Data) a
)
SELECT DISTINCT MIN(a.start) OVER(PARTITION BY a.State, a.[end] - ISNULL(b.start, a.start)) ,
MAX(a.[end] ) OVER(PARTITION BY a.State, a.[end] - ISNULL(b.start, a.start)) ,
a.state
,SUM(a.Seconds) OVER(PARTITION BY a.State, a.[end] - ISNULL(b.start, a.start))
FROM qry a LEFT JOIN qry b
ON a.rn + 1 = b.rn
AND a.[end] = b.start
Bigquery merge row where start date for one row is the end date for another
Consider below approach
select id, min(start_date) start_date, max(end_date) end_date, sum(amount) amount
from (
select *, countif(ifnull(new_group, true)) over (partition by id order by end_date) grp
from (
select *, start_date != lag(end_date) over(partition by id order by end_date) new_group
from your_table
)
)
group by id, grp
if applied to sample data in your question - output is
start date end date combine rows
You can do this in steps:
- Use a
jointo identify where two adjacent records should be combined. - Then do a cumulative sum to assign all such adjacent records a grouping identifier.
- Aggregate.
It looks like:
select id, min(startdt), max(enddte)
from (select t.*,
count(case when tprev.id is null then 1 else 0 end) over
(partition by t.idid
order by t.startdt
rows between unbounded preceding and current row
) as grp
from t left join
t tprev
on t.id = tprev.id and
t.startdt = tprev.enddt + interval '1 day'
) t
group by id, grp;
How to concatenate text in one column while merging rows with same start time and end time?
This is island and gap problem and you will need to use analytical function as following:
Select listagg(query_text,';') within group (order by start_time) as query_text,
username,
min(start_time) as start_time,
max(end_time) as end_time
From
(Select t.*,
Row_number() over (partition by username order by start_time)
- sum(case when start_time < prev_end_time or prev_end_time is null then 1 end) over (partition by username order by start_time) as grp
From (select t.*,
Lag(end_time) over (partition by username order by start_time) as prev_end_time
From your_table t
) t
)
Group by username, grp
Order by start_time;
Cheers!!
Merging Respective Start and End Dates, Setting Flag Depending on Start/End Date - SQL
This will return the matching end date for each start date.
select customer_id
,start_date
,lead(end_date) -- find the next row's end date
over (partition by customer_id
order by coalesce(start_date, end_date)) as new_end
,case when new_end is null then 'Y' else 'N' end as flag
from tab
qualify start_date is not null -- only return starting rows
order by 1,2;
How to find og combine rows who have continuous time columns
On approach to this is to identify each time a person starts a new food type. You can do this by recognizing that record does not overlap with any records before it. Then, groups of consecutive values are identified by counting the number of "starting" records before it.
This uses a cumulative sum. In SQL Server 2012+, you would do:
with cdf as (
select cd.*
(case when exists (select 1
from combinedates cd2
where cd2.id = cd.id and cd2.foodtype = cd.foodtype and
cd2.startdate < cd.startdate and
cd2.enddate >= cd.startdate
)
then 1 else 0
end) as StartFlag
from combinedates cd
)
select id, foodtype, min(startdate) as startdate, max(enddate) as enddate
from (select cdf.*,
sum(StartFlag) over (partition by id, foodtype order by startdate) as grp
from cdf
) cdf
group by id, foodtype, grp
A note about naming. A column called id should be a unique/primary key column in a table where it is defined. This is just an expectation of anyone reading the code, in the same way that a column called StartDate is not an integer. (I wouldn't have such a column, because my style is to have the primary key include the table name.)
In your table, this column should be called PersonId. I would define the table more like this:
CREATE TABLE PersonFoods (
PersonFoodId int identity(1, 1) not null primary key,
PersonId varchar(36),
FoodType varchar(10),
StartDate datetime,
EndDate datetime
);
And given that you are not using time components, the datetime values should perhaps be just date.
Merge Datetime Ranges Oracle SQL or PL/SQL
This is adapted from this answer which contains an explanation of the code. All that has changed is to add PARTITION BY order_id to calculate the date ranges for each order_id and then to return the ranges (rather than total the values, as per the linked answer):
SELECT order_id,
start_date_time,
end_date_time
FROM (
SELECT order_id,
LAG( dt ) OVER ( PARTITION BY order_id ORDER BY dt ) AS start_date_time,
dt AS end_date_time,
start_end
FROM (
SELECT order_id,
dt,
CASE SUM( value ) OVER ( PARTITION BY order_id ORDER BY dt ASC, value DESC, ROWNUM ) * value
WHEN 1 THEN 'start'
WHEN 0 THEN 'end'
END AS start_end
FROM table_name
UNPIVOT ( dt FOR value IN ( start_date_time AS 1, end_date_time AS -1 ) )
)
WHERE start_end IS NOT NULL
)
WHERE start_end = 'end';
From Oracle 12, you can use MATCH_RECONIZE to do row-by-row processing:
SELECT *
FROM table_name
MATCH_RECOGNIZE(
PARTITION BY order_id
ORDER BY start_date_time
MEASURES
FIRST(start_date_time) AS start_date_time,
MAX(end_date_time) AS end_date_time
ONE ROW PER MATCH
PATTERN (overlapping_rows* last_row)
DEFINE
overlapping_rows AS NEXT(start_date_time) <= MAX(end_date_time)
)
Which, for your test data:
CREATE TABLE table_name (
order_id NUMBER,
start_date_time DATE,
end_date_time DATE
);
INSERT INTO table_name ( order_id, start_date_time, end_date_time )
SELECT 3933, TIMESTAMP '2020-02-04 08:00:00', TIMESTAMP '2020-02-04 12:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-04 13:30:00', TIMESTAMP '2020-02-04 17:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-04 14:00:00', TIMESTAMP '2020-02-04 19:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-05 13:40:12', TIMESTAMP '2020-02-05 14:34:48' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-05 14:00:00', TIMESTAMP '2020-02-05 18:55:12' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-05 14:49:48', TIMESTAMP '2020-02-05 15:04:48' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-06 08:00:00', TIMESTAMP '2020-02-06 12:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-06 13:30:00', TIMESTAMP '2020-02-06 17:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-06 14:10:12', TIMESTAMP '2020-02-06 18:49:48' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-07 08:00:00', TIMESTAMP '2020-02-07 10:30:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-07 08:00:00', TIMESTAMP '2020-02-07 12:00:00' FROM DUAL UNION ALL
SELECT 3933, TIMESTAMP '2020-02-07 13:30:00', TIMESTAMP '2020-02-07 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-14 09:00:00', TIMESTAMP '2020-05-14 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-14 09:00:00', TIMESTAMP '2020-05-14 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-14 15:00:00', TIMESTAMP '2020-05-14 16:30:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 08:40:12', TIMESTAMP '2020-05-15 16:30:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 09:40:12', TIMESTAMP '2020-05-15 16:30:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 10:15:00', TIMESTAMP '2020-05-15 12:15:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-15 13:19:48', TIMESTAMP '2020-05-15 16:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 08:49:48', TIMESTAMP '2020-05-18 09:45:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 10:00:00', TIMESTAMP '2020-05-18 17:00:00' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 10:00:00', TIMESTAMP '2020-05-18 16:58:12' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 15:34:48', TIMESTAMP '2020-05-18 16:10:12' FROM DUAL UNION ALL
SELECT 11919, TIMESTAMP '2020-05-18 16:30:00', TIMESTAMP '2020-05-18 16:45:00' FROM DUAL;
Which both output:
ORDER_ID | START_DATE_TIME | END_DATE_TIME
-------: | :------------------ | :------------------
3933 | 2020-02-04 08:00:00 | 2020-02-04 12:00:00
3933 | 2020-02-04 13:30:00 | 2020-02-04 19:00:00
3933 | 2020-02-05 13:40:12 | 2020-02-05 18:55:12
3933 | 2020-02-06 08:00:00 | 2020-02-06 12:00:00
3933 | 2020-02-06 13:30:00 | 2020-02-06 18:49:48
3933 | 2020-02-07 08:00:00 | 2020-02-07 12:00:00
3933 | 2020-02-07 13:30:00 | 2020-02-07 17:00:00
11919 | 2020-05-14 09:00:00 | 2020-05-14 17:00:00
11919 | 2020-05-15 08:40:12 | 2020-05-15 16:30:00
11919 | 2020-05-18 08:49:48 | 2020-05-18 09:45:00
11919 | 2020-05-18 10:00:00 | 2020-05-18 17:00:00
db<>fiddle here
Related Topics
How to Escape Double Quotes Inside a SQL Fulltext 'Contains' Function
How to Return Empty Row from SQL Server
SQL Server Automatic Update Datetimestamp Field
Tips and Tricks to Speed Up an SQL
Merge Duplicate Temporal Records in Database
Performance of Inner Join VS Cartesian Product
Transpose a Row into Columns with MySQL Without Using Unions
Check Constraint of String to Contain Only Digits. (Oracle SQL)
Can Vba in Ms Access Using Parameter to Prevent SQL Injection
How to Prevent Deletion of the First Row in Table (Postgresql)
Convert Delimited String to Rows in Oracle
Converting a Time into 12 Hour Format in SQL
Expression Engine SQL Query Entries List by Authors
Compare Strings as Numbers in SQLite3
T-SQL Looping Through Xml Data Column to Derive Unique Set of Paths
Why Do SQL Id Sequences Go Out of Sync (Specifically Using Postgres)
Orderby in SQL Server to Put Positive Values Before Negative Values