用户调查报表

用户调查报表

代码实现自定义用户调查报表，显示上个月所有技术员的已发送调查数，被回答的调查数及平均满意度

以下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" , avg(srm.result) "Satisfaction Level" FROM Survey_Response_Main srm left JOIN SurveyResponseRequestMapping srrm  ON srm.RESPONSEID=srrm.RESPONSEID LEFT JOIN WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT  JOIN SDUser AS td ON wos.OWNERID = td.USERID LEFT  JOIN AaaUser AS ti ON td.USERID = ti.USER_ID LEFT JOIN Survey_Response_Comment srcmt ON srm.RESPONSEID=srcmt.RESPONSEID  where wo.CREATEDTIME >=<from_lastmonth> and wo.CREATEDTIME <=<to_lastmonth> group by ti.first_name order by "Satisfaction Level" desc

所得报表样式：

    • Related Articles

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

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

    • 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 ...