Report assistance
Good Afternoon.
Could I kindly ask on of the PostGre SQL Gods to assist with theis report? I's just like to edit it to reflect 'Current Month' instead of having to specify each time it is run. I'd like to turn it into a widget but makes no sense to edit all the time. Thanks in advance. Report Data is below.
Jason
SELECT ti.FIRST_NAME "Technician",
qd.QUEUENAME "Group",
count(wo.WORKORDERID) "Total Request",
count(case when std.ISPENDING='0' THEN 1 ELSE NULL END) "Completed Requests" ,
count(case when (std.ISPENDING='0' AND wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "SLA violated",
count(case when (std.ISPENDING='0' AND wos.ISOVERDUE='0') THEN 1 ELSE NULL END) "SLA not violated" ,
case when count(case when std.ISPENDING='0'THEN 1 ELSE NULL END) > 0 then count(case when (std.ISPENDING='0' and wos.ISOVERDUE='1') THEN 1 ELSE NULL END) *100 / count(case when std.ISPENDING='0'THEN 1 ELSE NULL END) else null end "% Violated",
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 "% 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 WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
WHERE (wo.ISPARENT='1') and wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2017-05-01 00:00:00') * 1000 AS BIGINT) AND wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2017-05-31 00:00:00') * 1000 AS BIGINT) GROUP BY ti.FIRST_NAME,qd.QUEUENAME order by 2