Query that returns the count of all distinct days between multiple start and end dates

I'm going to simplify the basis for asking the question. I have two tables in the ORACLE database: one called 'tb_vehicle' (id_vehicle, model, ...) and another table 'tb_trip' (id_vehicle, dt_start, dt_end, ...).

I am not able to carry out a query that returns the number of days that each vehicle was on the trip, excluding the repeated days (what do you mean? in the same day a vehicle can leave 1, 2, 3, ... times, however, I just need to know that the vehicle left on the day and not the number of times it left [I already made this query for another chart and it was very quiet]).

Example:

tb_vehicle

tb_trip

OBS: I am ignoring in this example the hours and minutes

intended result:

I was able to count the individual statistics for each vehicle using PL/SQL. But I need to perform this query only with SQL.

The system is developed at APEX.

Here's one option.

Sample data:

SQL> with
  2  tb_vehicle (id_vehicle, model) as
  3    (select 1, 'Chevrolet Cruze' from dual union all
  4     select 2, 'Chevrolet Sonic' from dual
  5    ),
  6  tb_trip (id_vehicle, dt_start, dt_end) as
  7    (select 1, date '2023-06-05', date '2023-06-05' from dual union all
  8     select 1, date '2023-06-05', date '2023-06-05' from dual union all
  9     select 1, date '2023-06-05', date '2023-06-08' from dual union all
 10     select 2, date '2023-06-05', date '2023-06-06' from dual
 11    ),

Query begins here; temp CTE lists all dates between start and end date; the final query calculates distinct dates per vehicle (along with join to tb_vehicle table):

 12  temp as
 13    (select t.id_vehicle, t.dt_start + column_value - 1 datum
 14     from tb_trip t cross join
 15       table(cast(multiset(select level from dual
 16                           connect by level <= dt_end - dt_start + 1
 17                          ) as sys.odcinumberlist))
 18    )
 19  select a.id_vehicle, m.model, count(distinct a.datum) qtd_Days
 20  from temp a join tb_vehicle m on m.id_vehicle = a.id_vehicle
 21  group by a.id_Vehicle, m.model
 22  order by a.id_Vehicle;

ID_VEHICLE MODEL             QTD_DAYS
---------- --------------- ----------
         1 Chevrolet Cruze          4
         2 Chevrolet Sonic          2

SQL>

Here is Littlefoot's idea with a slightly different query. I am using a standard recursive CTE to generate the single days from the rows first. Then I count the distinct days per vehicle. At last I join this result to the vehicle table. You may find this query a tad easier to understand.

with single_days(id_vehicle, dt, dt_end) as
(
  select id_vehicle, dt_start, dt_end from tb_trip
  union all
  select id_vehicle, dt + 1, dt_end from single_days where dt < dt_end
)
, counted_days as
(
  select id_vehicle, count(distinct dt) as days
  from single_days
  group by id_vehicle
)
select *
from tb_vehicle v
join counted_days c using (id_vehicle);

Demo: https://dbfiddle.uk/paJKama3

Generating a list of all the days and then finding the distinct days is simple to code but if you have large ranges then it will become costly.

Instead you can merge the ranges (by UNPIVOTing the dates and then using MATCH_RECOGNIZE to merge the overlaps) and then using arithmetic to calculate the size of the range and aggregating for each vehicle:

SELECT v.id_vehicle,
       MAX(v.model) AS model,
       SUM(t.dt_end - t.dt_start + 1) AS qtd_days
FROM   tb_trip
       UNPIVOT (
         dt FOR type IN (dt_start AS 1, dt_end AS -1)
       )
       MATCH_RECOGNIZE(
         PARTITION BY id_vehicle
         ORDER BY dt, type DESC
         MEASURES
           FIRST(dt) AS dt_start,
           LAST(dt)  AS dt_end
         PATTERN ( overlaps+ range_end )
         DEFINE
           overlaps  AS SUM(type) > 0,
           range_end AS SUM(type) = 0
       ) t
       INNER JOIN tb_vehicle v
       ON (t.id_vehicle = v.id_vehicle)
GROUP BY v.id_vehicle

Which, for the sample data:

CREATE TABLE tb_vehicle (id_vehicle, model) AS
SELECT 1, 'Chevrolet Cruze' FROM DUAL UNION ALL
SELECT 2, 'Chevrolet Sonic' FROM DUAL UNION ALL
SELECT 3, 'DeLorean' FROM DUAL;

CREATE TABLE tb_trip (id_vehicle, dt_start, dt_end) AS
SELECT 1, DATE '2023-06-05', DATE '2023-06-05' FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-05', DATE '2023-06-05' FROM DUAL UNION ALL
SELECT 1, DATE '2023-06-05', DATE '2023-06-08' FROM DUAL UNION ALL
SELECT 2, DATE '2023-06-05', DATE '2023-06-06' FROM DUAL UNION ALL
SELECT 3, DATE '1885-01-01', DATE '9999-12-31' FROM DUAL;

Outputs:

fiddle

I hope the code below can meet your need :

select v.id
       ,v.model
       ,sum(case when DT_END<>DT_START then DT_END - DT_START + 1 else 0 end) qtd_days
from tb_trip t,
    tb_vehicle v
where t.id_vehicle = v.id
group by v.id ,v.model 
order by v.id

Simpler version using MATCH_RECOGNIZE

with tb_trip(id_vehicle, dt_start, dt_end) as (
	select 1, to_date('06-05-2023', 'mm-dd-yyyy'), to_date('06-05-2023', 'mm-dd-yyyy') from dual union all
	select 1, to_date('06-05-2023', 'mm-dd-yyyy'), to_date('06-05-2023', 'mm-dd-yyyy') from dual union all
	select 1, to_date('06-05-2023', 'mm-dd-yyyy'), to_date('06-08-2023', 'mm-dd-yyyy') from dual union all
	select 2, to_date('06-05-2023', 'mm-dd-yyyy'), to_date('06-06-2023', 'mm-dd-yyyy') from dual -- union all
),
tb_vehicle(id_vehicle, model) as (
	select 1, 'Chevrolet Cruze' from dual union all
	select 2, 'Chevrolet Sonic' from dual
)
select d.id_vehicle, v.model, sum(dt_end - dt_start +1) as days
from (
	select * from tb_trip
	match_recognize (
		partition by id_vehicle
		order by dt_start, dt_end
		measures first(dt_start) as dt_start, max(dt_end) as dt_end
		pattern( overlaps* strt )
		define
			overlaps as dt_end >= dt_start
	)
) d
join tb_vehicle v on v.id_vehicle = d.id_vehicle
group by d.id_vehicle, v.model
;