MSSQL-工单量-工单按时响应率-工单按时解决率-二次开单率-满意度 报表

MSSQL-工单量-工单按时响应率-工单按时解决率-二次开单率-满意度 报表

报表格式如图：


SELECT  
case when "ti"."FIRST_NAME" is not null then "ti"."FIRST_NAME" else '合计' end "工程师名",
count ("wo"."WORKORDERID") AS "工单受理量",
cast ( cast (100.0*sum(case when "wos"."IS_FR_OVERDUE"= 0 then 1 else 0 end)/count("wos"."IS_FR_OVERDUE") as decimal(18,2)) as varchar(6)) AS "工单按时响应率(%)",
cast ( cast (100.0*sum(case when "wos"."ISOVERDUE"= 0 then 1 else 0 end)/count("wos"."ISOVERDUE") as decimal(18,2)) as varchar(6)) AS "工单按时解决率(%)",
cast ( cast (100.0*sum(case when "wos"."REOPENED"= 1 then 1 else 0 end)/count("wos"."REOPENED") as decimal(18,2)) as varchar(6)) AS "二次开单率(%)",
cast ( avg (satis.satis)as decimal(18,2)) as "满意度(%)"
FROM "WorkOrder" "wo" 
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID" 
LEFT JOIN "SDUser" "td" ON "wos"."OWNERID"="td"."USERID" 
LEFT JOIN "AaaUser" "ti" ON "td"."USERID"="ti"."USER_ID" 
LEFT JOIN "WorkOrder_Fields" "wof" ON "wo"."WORKORDERID"="wof"."WORKORDERID"
LEFT JOIN 
(select wos.OWNERID, AVG(cast(ANSWER as decimal(18,2))) as "satis" 
from "WorkOrder" "wo"
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID" 
LEFT JOIN SurveyResponseRequestMapping srrm ON wo.WORKORDERID=srrm.WORKORDERID
LEFT JOIN Survey_Response_Answer sra ON srrm.responseid=sra.responseid group by wos.OWNERID) satis ON wos.OWNERID=satis.OWNERID
group by "ti"."FIRST_NAME" 
with rollup

    • Related Articles

    • SDP 删除满意度调查某条数据

      需求：删除满意度视图中的某条数据。 点击查看请求id，基于请求id（工单ID）查找表中对应的responseid。 select * from SurveyResponseRequestMapping where workorderid=98;  然后在主表中删除这条数据 Survey_Response_Main  连接进入到数据库后执行删除操作即可删除视图。 delete from Survey_Response_Main where responseid=1202;

    • 用户调查报表

      代码实现自定义用户调查报表，显示上个月所有技术员的已发送调查数，被回答的调查数及平均满意度 以下sql语句适用于SQL server 2017： SELECT   ti.first_name "Technician",COUNT(srm.RESPONSEID) "NO of Survey sent", COUNT(case when responsetime is not null then 1 else null end) "No of Survey Answered" , ...

    • SDP 工单满意度调查报表

      效果图 实现步骤 一、需要收集的信息 1.1、使用的数据库类型； 1.2、Select * from Survey_Question 的查询结果； 如： 1.3、调查配置详情截图； 如： 1.4、通过自定义报表，获取已满足需求的除满意度调查的 SQL 如： SELECT "wo"."WORKORDERID" AS "请求ID", "ti"."FIRST_NAME" AS "作业技术员" FROM "WorkOrder" "wo" LEFT JOIN "WorkOrderStates" "wos" ...

    • EC - Sql - 计划报表发送电源详细报表方法

      1. 查询报表使用SQL PGSQL SELECT Resource.NAME AS "Computer Name", Resource.DOMAIN_NETBIOS_NAME AS "Domain Name", FORMAT(DATEADD(SECOND, PH.DATE / 1000, '19700101'), 'MMM dd, yyyy') AS "Date", FORMAT(DATEADD(MILLISECOND, PH.BOOTUP_TIME, '00:00:00'), ...

    • CI关系报表

      MSSQL： SELECT ci.ciname AS 'Name', (SELECT rel.relationship + Char(10) + ' ' + ci2.ciname + Char(10) FROM cirelationships AS cirel LEFT JOIN ci AS ci2 ON ci2.ciid = cirel.ciid2 LEFT JOIN relationshiptype AS rel ON rel.relationshiptypeid = ...