Hi,
GLIP is used for "La Banque Postal" to manage and follow action on the network.
BT's staff take charge of that, and some indicator may be useful to control the
answer speed and accuracy.
To do that, we try to implement two indicators:
1) Rate of DDT accepted (from status "nouveau" to status "En cours (Attribué)")
in lease that 48 hours
I try this:
SELECT X.qualif, COUNT(*) AS Cumul FROM (SELECT distinct f.tracking AS ID,
t.date AS start_ddt, DATE_FORMAT(f.date, "%Y-%m-%d %H:%i:%s") AS end_ddt,
IF (TIMESTAMPDIFF(MINUTE, t.date, f.date) > 48*60, "Bad", "Good") AS qualif
FROM glpi_followups f INNER JOIN glpi_tracking t
ON (f.tracking = t.ID)
WHERE f.contents like 'Changement de statut de la DDT: En cours (Attrib%'
OR f.contents like 'Changement de groupe de la DDT: Pas de groupe -> A%'
GROUP BY f.tracking) X
GROUP BY X.qualif
but this miss a lot of DDT...
2) Rate of DDT realized (from status "nouveau" other status like "En attente"
"Fermé (résolu)" "Fermé (non résolu)" or "En cours (Plannifié)" In fact, we
take care of the first change of status which is not just accepted
To do this:
CREATE VIEW withChangedStatus AS
SELECT distinct f.tracking AS ID, f.date AS end_ddt
FROM glpi_followups f
WHERE f.contents like 'Changement de statut de la DDT:%'
AND NOT f.contents like 'Changement de statut de la
DDT: En cours (Attrib%'
AND NOT f.contents like 'Changement de statut de la
DDT: Ferm%'
GROUP BY f.tracking;
and :
SELECT X.qualif, COUNT(*) AS Cumul FROM ((SELECT t.ID AS ID, t.date AS
start_ddt, DATE_FORMAT(w.end_ddt, "%Y-%m-%d %H:%i:%s") AS end_ddt, IF
(TIMESTAMPDIFF(MINUTE, t.date, w.end_ddt) > 48*60, "Bad", "Good") AS qualif
FROM glpi_tracking t INNER JOIN withChangedStatus w
ON (t.ID = w.ID))
UNION
(SELECT t.ID AS ID, t.date AS start_ddt,
CASE t.closedate WHEN "0000-00-00 00:00:00" THEN DATE_FORMAT(UTC_TIMESTAMP(),
"%Y-%m-%d %H:%i:%s") ELSE DATE_FORMAT(t.closedate, "%Y-%m-%d %H:%i:%s") END AS
end_ddt,
IF (
CASE t.closedate WHEN "0000-00-00 00:00:00" THEN TIMESTAMPDIFF(MINUTE, t.date,
UTC_TIMESTAMP()) ELSE TIMESTAMPDIFF(MINUTE, t.date, t.closedate) END > 48*60,
"Bad", "Good") AS qualif
FROM glpi_tracking t
WHERE t.ID NOT IN (SELECT w.ID FROM withChangedStatus w))
ORDER BY ID) X
On this, I miss some ddt
can-you help my ? Thanks to answer
Regards, Pascal
_______________________________________________
Glpi-dev mailing list
[email protected]
https://mail.gna.org/listinfo/glpi-dev
