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 29 30 31 32 33 34
| SELECT pbm.hotliner AS Hotliner
, pbm.codegamme AS Gamme
, COUNT(*) AS "Nombre d incidents"
, COUNT(*) * 100 / tot.cnt AS Pourcentage
FROM problems AS pbm
INNER JOIN
( SELECT codegamme
, COUNT(*) AS cnt
FROM problems
WHERE datecloture BETWEEN DATE '2007-09-10' AND DATE '2007-09-15'
GROUP BY codegamme
) AS tot
ON tot.codegamme = pbm.codegamme
WHERE pbm.datecloture BETWEEN DATE '2007-09-10' AND DATE '2007-09-15'
AND pbm.codegamme NOT IN ('ETU-3270','ETU-ADV','ETU-FIDEL','ETU-MESIR','ETU-PRE')
GROUP BY pbm.codegamme
, pbm.hotliner
, tot.cnt
UNION ALL
SELECT hln.hotliner
, gam.gamme
, 0
, 0
FROM hotliners AS hln
CROSS JOIN
gammes AS gam
WHERE NOT EXISTS
( SELECT 1
FROM problems AS pbs
WHERE pbs.hotliner = hln.hotliner
AND pbs.codegamme = gam.gamme
AND pbs.datecloture BETWEEN DATE '2007-09-10' AND DATE '2007-09-15' )
ORDER BY hotliner
; |
Partager