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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
| (select ifnull(tabMaj.mois, ifnull(tabBl.mois, tabMin.mois)) as mois, tabBl.nb_Bl, tabMaj.nb_Maj, tabMin.nb_Min
from
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 ) as mois,
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Bl from items
where items.space_id = 2
and items.cus_int_03 = 1
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 1
group by month(items.time_stamp)) as tabBl left outer join
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 ) as mois,
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Maj from items
where items.space_id = 2
and items.cus_int_03 = 1
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 2
group by month(items.time_stamp)) as tabMaj on tabBl.nummois = tabMaj.nummois
left outer join
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 ) as mois,
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Min from items
where items.space_id = 2
and items.cus_int_03 = 1
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 3
group by month(items.time_stamp)) as tabMin on tabMin.nummois = tabMaj.nummois)
union
(select ifnull(tabMaj.mois, ifnull(tabBl.mois, tabMin.mois)) as mois, tabBl.nb_Bl, tabMaj.nb_Maj, tabMin.nb_Min
from
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 ) as mois,
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Bl from items
where items.space_id = 2
and items.cus_int_03 = 2
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 1
group by month(items.time_stamp)) as tabBl right outer join
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 ) as mois,
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Maj from items
where items.space_id = 2
and items.cus_int_03 = 1
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 2
and month(items.time_stamp) < month(STR_TO_DATE('30/04/2009', '%d/%m/%Y') ) + 1
group by month(items.time_stamp)) as tabMaj on tabBl.nummois = tabMaj.nummois
right outer join
(select CONVERT(concat(month(items.time_stamp),'/',year(items.time_stamp) ) USING utf8 ) as mois,
month(items.time_stamp) as nummois, count(distinct items.sequence_num) as nb_Min from items
where items.space_id = 2
and items.cus_int_03 = 1
and year(items.time_stamp)= year(now())
and items.cus_int_02 in (1,3,4)
and items.severity = 3
group by month(items.time_stamp)) as tabMin on tabMin.nummois = tabMaj.nummois)
order by mois. |
Partager