1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| SELECT r.DATETRAIN
, r.DEPART_GARE_ID
, r.ARRIVE_GARE_ID
, r.IDUA
, t.NBTRAINPROG AS "NB Trains programmés"
, COUNT(*) AS "NB Trains Réalisés"
, COALESCE(S.CNT) / COUNT(*) AS "Ponctualité"
FROM TRAIN r
INNER JOIN
TRAJET t
ON r.DEPART_GARE_ID = t.IDGAREDEPART
AND r.ARRIVE_GARE_ID = t.IDGAREARRIVEE
LEFT JOIN
( SELECT IDTRAIN
, COUNT(*) AS cnt
FROM STAGAREATEMPS
WHERE (DEPARTRE - DEPARTPL) * 24 * 60 BETWEEN 0 AND 15
OR (ARRIVERE - ARRIVEPL) * 24 * 60 BETWEEN 0 AND 15
GROUP BY IDTRAIN
) S
ON s.IDTRAIN = r.IDTRAIN
GROUP BY r.DATETRAIN
, r.IDUA
, r.DEPART_GARE_ID
, r.ARRIVE_GARE_ID
, t.NBTRAINPROG
, S.CNT
; |
Partager