I have two tables. One is ACTUAL_FLIGHTS and other is SCHEDULED_FLIGHTS.

ACTUAL_FLIGHTS:

SCHEDULED_FLIGHTS

Now I need to count number of days between last flight and the next scheduled flight for each Aircraft. Resulting table should look like this:

I've tried this query, but didn't get intended result, even it frezees while generating result:

SELECT s.AC, 
       MIN(s.SCHEDULE_DATE) - MAX(f.FLIGHT_DATE)
FROM SCHEDULE_FLIGHT s
INNER JOIN FLIGHT_DATE f ON f.AC = s.AC
HAVING MIN(s.SCHEDULE_DATE) >= MAX(f.FLIGHT_DATE)
GROUP BY s.AC;

CodePudding user response：

SELECT s.AC, 
       MIN(s.SCHEDULE_DATE) - MAX(f.FLIGHT_DATE)
FROM SCHEDULE_FLIGHT s
INNER JOIN FLIGHT_DATE f 
   ON f.AC = s.AC 
  AND s.SCHEDULE_DATE >= f.FLIGHT_DATE
GROUP BY s.AC;

CodePudding user response：

You can use this.

SELECT S.aircraft, MIN(S.schedule_date - A.flight_date) DATE_DIFF
FROM actual_flights A  INNER JOIN scheduled_flights S 
ON S.aircraft = A.aircraft 
GROUP BY S.aircraft
ORDER BY S.aircraft;