Contrôle nombre de jours max entre deux trips (Laurent)

Vérifier le nombre de jours en deux marées pour détecter des marées manquantes

SQL de Laurent : 

select *
from
(
       select program,
       --COUNT(distinct topiaid) as nb_topiaid,
       ocean, flag, vessel_type, landing_year, vessel_name, departure, port_departure, arrival - departure  as nb_days_in_trip,
       arrival, port_arrival,
       (
       select min(t2.startdate) - arrival from ps_common.trip as t2
       INNER JOIN common.vessel AS v2 ON t2.vessel = v2.topiaid
       INNER JOIN ps_common.program lp ON (t2.logbookprogram = lp.topiaid)
       where v2.label1 = trips.vessel_name
       and t2.startdate >= trips.arrival
       ) as nb_days_with_next_trip,
       (
       select min(t2.startdate) from ps_common.trip as t2
       INNER JOIN common.vessel AS v2 ON t2.vessel = v2.topiaid
       INNER JOIN ps_common.program lp ON (t2.logbookprogram = lp.topiaid)
       where v2.label1 = trips.vessel_name
       and t2.startdate >= trips.arrival
       ) as next_departure
       from
       (
                          select
                          t.topiaid,
                          lp.label2 as program,
                          o.label1 AS ocean,
                          ct1.iso3code AS flag,
                          CASE
                          WHEN vt.code::numeric IN (1,2,3)     THEN 'BB'
                          WHEN vt.code::numeric IN (4,5,6)     THEN 'PS'
                          WHEN vt.code::numeric IN (7)         THEN 'LL'
                          WHEN vt.code::numeric IN (10)        THEN 'SV'
                          ELSE 'OTH'
                          END AS vessel_type,
                           extract(year FROM t.enddate)::integer AS landing_year,
                          t.startdate as departure,
                          h1.label1 as port_departure,
                          t.enddate as arrival,
                          h2.label1 as port_arrival,
                          r.date AS activity_date,
                          a.number,
                          ct2.iso3code AS landing_country,
                          v.cfrid,
                          v.label1 AS vessel_name
                          FROM
                           ps_common.trip AS t
                          INNER JOIN common.ocean AS o ON t.ocean = o.topiaid
                          INNER JOIN common.harbour AS h1 ON t.departureharbour = h1.topiaid
                          INNER JOIN common.harbour AS h2 ON t.landingharbour = h2.topiaid
                          INNER JOIN common.country AS ct2 ON h1.country = ct2.topiaid
                          INNER JOIN common.vessel AS v ON t.vessel = v.topiaid
                          INNER JOIN common.vesseltype AS vt ON v.vesseltype = vt.topiaid
                          INNER JOIN common.country AS ct1 ON v.flagcountry = ct1.topiaid
                          INNER JOIN ps_logbook.route AS r ON r.trip = t.topiaid
                          INNER JOIN ps_logbook.activity AS a ON a.route = r.topiaid
                          INNER JOIN ps_common.program lp ON (t.logbookprogram = lp.topiaid)
       ) as trips
       group by program, ocean, flag, vessel_type, vessel_name,
       landing_year, departure, port_departure, arrival, port_arrival
       order by ocean, flag, vessel_type, vessel_name, landing_year, departure, port_departure, arrival, port_arrival
) as list
where nb_days_with_next_trip > 30
and landing_year >= 2024;