Version : 10609

DB : PGSQL / MSSQL

OUTPUT :

SELECT ad.org_name "Account",
count(wo.WORKORDERID) "Total Request",
count(case when std.ISPENDING='0' THEN 1 ELSE NULL END) "Completed Requests" ,
count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "SLA violated",
count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) "SLA not violated" ,
case when count(wo.workorderid) > 0 then count(case when ( wos.ISOVERDUE='1') THEN 1 ELSE NULL END) *100 / count(wo.workorderid) else null end "% Violated",
case when count(wo.workorderid) > 0 then count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) *100 /count(wo.workorderid) else null end "% not Violated",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE NULL END) *100 as float) / cast(count(wo.workorderid) as float) else null end "Response % Violated",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.IS_FR_OVERDUE='0') THEN 1 ELSE NULL END) *100 as float) / cast(count(wo.workorderid) as float) else null end "Response % not Violated" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std on wos.statusid=std.statusid
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
left join accountsitemapping asm on asm.siteid = wo.siteid
Left join accountdefinition ad on asm.accountid = ad.org_id
WHERE (wo.ISPARENT='1') GROUP BY ad.org_name order by 1