I have a table having data like this:

I need to get the starting and ending point of each vehicle based on the date.

For example, Truck A has started from point A to point B on 02/01/2021 01:00:00. Then Truck A has moved from point B to point C on 02/01/2021 02:00:00. Truck A has moved from point C to point D on 04/01/2021 03:00:00.

It has started from point A and ended at point D.

I want to get a result like this:

CodePudding user response：

Use GROUP BY and aggregate using KEEP to get the FIRST or LAST date values:

SELECT vehicle,
       MIN("START") KEEP (DENSE_RANK FIRST ORDER BY "DATE") AS "START",
       MAX("END")   KEEP (DENSE_RANK LAST  ORDER BY "DATE") AS "END",
       MAX("DATE") AS "DATE"
FROM   table_name
GROUP BY vehicle

Which, for the sample data:

CREATE TABLE table_name (Vehicle, "START", "END", "DATE") AS
SELECT 'Truck A', 'A', 'B', DATE '2021-01-02'   INTERVAL '1' HOUR FROM DUAL UNION ALL
SELECT 'Truck A', 'B', 'C', DATE '2021-01-02'   INTERVAL '2' HOUR FROM DUAL UNION ALL
SELECT 'Truck A', 'C', 'D', DATE '2021-01-04'   INTERVAL '3' HOUR FROM DUAL UNION ALL
SELECT 'Truck B', 'C', 'A', DATE '2021-01-05'   INTERVAL '1' HOUR FROM DUAL UNION ALL
SELECT 'Truck B', 'A', 'B', DATE '2021-01-06'   INTERVAL '1' HOUR FROM DUAL UNION ALL
SELECT 'Truck C', 'C', 'B', DATE '2021-01-07'   INTERVAL '1' HOUR FROM DUAL UNION ALL
SELECT 'Truck C', 'B', 'C', DATE '2021-01-08'   INTERVAL '1' HOUR FROM DUAL UNION ALL
SELECT 'Truck C', 'C', 'B', DATE '2021-01-09'   INTERVAL '1' HOUR FROM DUAL UNION ALL
SELECT 'Truck C', 'B', 'A', DATE '2021-01-10'   INTERVAL '1' HOUR FROM DUAL

Outputs:

VEHICLE	START	END	DATE
Truck A	A	D	2021-01-04 03:00:00
Truck B	C	B	2021-01-06 01:00:00
Truck C	C	A	2021-01-10 01:00:00

db<>fiddle

SELECT Vehicle
, FirstStart AS "Start"
, LastEnd AS "End"
, MAX("Date") AS "Date"
FROM
(
  SELECT Vehicle, "Start", "End", "Date"
  , FIRST_VALUE("Start") OVER (PARTITION BY Vehicle ORDER BY "Date") AS FirstStart
  , FIRST_VALUE("End") OVER (PARTITION BY Vehicle ORDER BY "Date" DESC) AS LastEnd
  FROM yourtable
) q
GROUP BY Vehicle, FirstStart, LastEnd
ORDER BY Vehicle;