Query to get Monthly data of request and sla breach count based on additional fields

Query to get Monthly data of request and sla breach count based on additional fields

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

                New to ADSelfService Plus?