Untitled

mail@pastecode.io avatar
unknown
plain_text
7 months ago
853 B
2
Indexable
Never
SELECT
    a.id_asset,
    a.serial_number_1 AS serial_number,
    a.id_produit,
    a.actif,
    MIN(ra.date_debut_maintenance) AS first_maintenance_start_date,
    MAX(ra.date_fin_maintenance) AS last_maintenance_end_date,
    CASE
        WHEN MAX(ra.date_fin_maintenance) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 MONTH) THEN
            CONCAT('Dans les 3 prochains mois (', DATEDIFF(MAX(ra.date_fin_maintenance), CURDATE()), ' jours restants)')
        WHEN MAX(ra.date_fin_maintenance) >= CURDATE() THEN 'En cours'
        ELSE 'Dépassé'
    END AS status
FROM
    tblSOCIETE s
JOIN
    tblASSETS a ON s.id_societe = a.id_societe
LEFT JOIN
    tblLIGNEDEVIS ra ON a.id_asset = ra.id_asset
WHERE
    s.id_societe = 258;
GROUP BY
    a.id_asset, a.serial_number_1, a.id_produit, a.actif
ORDER BY
    a.id_asset;
Leave a Comment