Hi I have a requirement in my project
details and sample. data are as follows.
CREATE TABLE "TEST2"
( "ID_NBR" NUMBER,
ORDER_ID NUMBER,
"START_DATE" DATE,
"END_DATE" DATE
);
INSERT INTO test2 (
id_nbr,
order_id,
start_date,
end_date
) VALUES (
1,
101,
TO_DATE('09-OCT-25', 'DD-MON-RR'),
TO_DATE('18-OCT-25', 'DD-MON-RR')
);
INSERT INTO test2 (
id_nbr,
order_id,
start_date,
end_date
) VALUES (
2,
101,
TO_DATE('02-OCT-25', 'DD-MON-RR'),
TO_DATE('09-OCT-25', 'DD-MON-RR')
);
INSERT INTO test2 (
id_nbr,
order_id,
start_date,
end_date
) VALUES (
3,
101,
TO_DATE('15-SEP-25', 'DD-MON-RR'),
TO_DATE('02-OCT-25', 'DD-MON-RR')
);
INSERT INTO test2 (
id_nbr,
order_id,
start_date,
end_date
) VALUES (
4,
100,
TO_DATE('21-AUG-25', 'DD-MON-RR'),
TO_DATE('29-AUG-25', 'DD-MON-RR')
);
INSERT INTO test2 (
id_nbr,
order_id,
start_date,
end_date
) VALUES (
5,
100,
TO_DATE('15-AUG-25', 'DD-MON-RR'),
TO_DATE('21-AUG-25', 'DD-MON-RR')
);
INSERT INTO test2 (
id_nbr,
order_id,
start_date,
end_date
) VALUES (
6,
100,
TO_DATE('15-AUG-25', 'DD-MON-RR'),
TO_DATE('21-AUG-25', 'DD-MON-RR')
);
INSERT INTO test2 (
id_nbr,
order_id,
start_date,
end_date
) VALUES (
7,
100,
TO_DATE('14-AUG-25', 'DD-MON-RR'),
TO_DATE('15-AUG-25', 'DD-MON-RR')
);
INSERT INTO test2 (
id_nbr,
order_id,
start_date,
end_date
) VALUES (
8,
100,
TO_DATE('31-OCT-24', 'DD-MON-RR'),
TO_DATE('05-NOV-24', 'DD-MON-RR')
);
INSERT INTO test2 (
id_nbr,
order_id,
start_date,
end_date
) VALUES (
9,
100,
TO_DATE('07-JUN-24', 'DD-MON-RR'),
TO_DATE('21-JUN-24', 'DD-MON-RR')
);
INSERT INTO test2 (
id_nbr,
order_id,
start_date,
end_date
) VALUES (
10,
100,
TO_DATE('04-JUN-24', 'DD-MON-RR'),
TO_DATE('07-JUN-24', 'DD-MON-RR')
);
output is select * from test2
order by 1
expected output is as below the latest of each order without gaps
eg.
id 7 with start date 14th aug 2025 continued upto order end date 29 august 2025 without gaps and the latest
there is no gap between start and enddates it is just continuing to next id on the same end date or on the same order start date. that is the key.
and is the same case with another order id 101
order 100 as below
7 100 14-aug 2025 29-aug-2025
order 101 as below
3 101 15-sep-2025 18-oct-2025
how can i get this using sql query
please advise.
thanks in advance.