SELECT YEAR(t.date_creation) as 'Year', count(*) as 'Volume of tickets'
FROM glpi_tickets t
JOIN glpi_entities e on t.entities_id = e.id
WHERE e.completename LIKE "Root entity"
AND YEAR(t.date_creation) > YEAR(CURRENT_DATE()) - 5
GROUP BY YEAR(t.date_creation)
UNION
SELECT
CONCAT('Estimated total end ', YEAR(CURRENT_DATE())),
ROUND(
(
SELECT count(*)
FROM glpi_tickets t4
JOIN glpi_entities e4 on t4.entities_id = e4.id
WHERE e4.completename LIKE "Entité racine" (root entity)
AND YEAR(t4.date_creation) = YEAR(CURRENT_DATE()) - 1
) *
(
(
SELECT count(*)
FROM glpi_tickets t3
JOIN glpi_entities e3 on t3.entities_id = e3.id
WHERE e3.completename LIKE "Root entity"
AND YEAR(t3.date_creation) = YEAR(CURRENT_DATE())
)
/
(
SELECT count(*)
FROM glpi_tickets t2
JOIN glpi_entities e2 on t2.entities_id = e2.id
WHERE e2.completename LIKE "Root entity"
AND YEAR(t2.date_creation) = (YEAR(CURRENT_DATE()) - 1)
AND (
MONTH(t2.date_creation) < MONTH(CURRENT_DATE())
OR MONTH(t2.date_creation) = MONTH(CURRENT_DATE()) AND DAY(t2.date_creation) <= DAY(CURRENT_DATE())
)
)
)
)
