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 tt.Nom_dd, sum(tt.cn)AS NombreInc, sum(tt.c) AS NombreAcci,sum(tt.r) AS NombreTravaux FROM
(
(SELECT p.Nom_dd, count(*) AS cn, 0 AS c, 0 as r
FROM Incident i
JOIN DP p ON (p.Nom_dp = i.Nom_dp)
WHERE CONVERT ( varchar(10) , i.Date_cou,103) = '01/01/2010'
GROUP BY p.Nom_dd, i.Date_cou
)
union
(SELECT p.Nom_dd, 0, count(*), 0
FROM Accident a
JOIN DP p ON p.Nom_dp = a.Nom_dp
WHERE CONVERT ( varchar(10) , a.Date_acc,103) = '26/05/2011'
GROUP BY p.Nom_dd, a.Date_acc
)
UNION
(SELECT Nom_dd, 0, 0, count(*)
FROM Travaux
WHERE CONVERT ( varchar(10) , DateCou_tra,103) = '27/05/2011'
GROUP BY Nom_dd, DateCou_tra
)
)AS tt
GROUP BY tt.Nom_dd |
Partager