Version : 11027

DB: Postgres

OUTPUT:

SELECT wof.UDF_CHAR2 AS "Issue Type",
EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)):: int "Year",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=1 THEN 1 ELSE NULL END) "JAN",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=2 THEN 1 ELSE NULL END) "Feb",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=3 THEN 1 ELSE NULL END) "Mar",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=4 THEN 1 ELSE NULL END)"Apr",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=5 THEN 1 ELSE NULL END)"May",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=6 THEN 1 ELSE NULL END)"June",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=7 THEN 1 ELSE NULL END)"July",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=8 THEN 1 ELSE NULL END)"Aug",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=9 THEN 1 ELSE NULL END)"Sep",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=10 THEN 1 ELSE NULL END)"Oct",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=11 THEN 1 ELSE NULL END)"Nov",
 Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=12 THEN 1 ELSE NULL END)"Dec",
 Count(EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))) "Count",
 count(case when (std.ISPENDING='0' AND wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "Breach",
 count(case when (std.ISPENDING='0' AND wos.ISOVERDUE='0') THEN 1 ELSE NULL END) "Pass" ,
 case when count(case when std.ISPENDING='0'THEN 1 ELSE NULL END) > 0 then count(case when (std.ISPENDING='0' and wos.ISOVERDUE='0') THEN 1 ELSE NULL END) *100 / count(case when std.ISPENDING='0'THEN 1 ELSE NULL END) else null end "% Pass",
 case when case when count(case when std.ISPENDING='0'THEN 1 ELSE NULL END) > 0 then count(case when (std.ISPENDING='0' and wos.ISOVERDUE='0') THEN 1 ELSE NULL END) *100 / count(case when std.ISPENDING='0'THEN 1 ELSE NULL END) else null end > 97 then 'Execellent' when case when count(case when std.ISPENDING='0'THEN 1 ELSE NULL END) > 0 then count(case when (std.ISPENDING='0' and wos.ISOVERDUE='0') THEN 1 ELSE NULL END) *100 / count(case when std.ISPENDING='0'THEN 1 ELSE NULL END) else null end > 91 then 'Moderate' else 'Poor' end as "Rating"
 from workorder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std on wos.statusid=std.statusid
Inner JOIN workorder_fields wof ON wos.workorderid=wof.workorderid
group by EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)),wof.UDF_CHAR2 order by 1