Report on Requests Resolved/Closed by a Different Engineer within time ranges like 1hr, 4hr ,8hr etc..

Dear User,

Please find the query below to generate on:

"No of calls resolved or closed by the a different engineer within - 1hour, 4 hour, 8 hour, 12 hour, 24 hour, 2 days, 5 day, 10 day, 20 days, 30 days, 45 days, and more Grouped by Impact"

Tested in : MsSQL (SDP 8016) ,if you need any clarification please get back to us.

Note : Please change the Date filter (marked in Bold Blue color) as per your requirement

SELECT

Impact.NAME 'Impact',

SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(60*60*1000) <= 1 THEN 1 ELSE 0 END) '1 hr',

SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(60*60*1000) > 1 AND (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(60*60*1000) <= 4 THEN 1 ELSE 0 END ) '1-4',

SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(60*60*1000) > 4 AND (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(60*60*1000) <= 8 THEN 1 ELSE 0 END ) '4-8 hrs',

SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(60*60*1000) > 8 AND (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(60*60*1000) <= 12 THEN 1 ELSE 0 END ) '8-12 hrs',

SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(60*60*1000) > 12 AND (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(60*60*1000) <= 24 THEN 1 ELSE 0 END ) '12-24 hrs',

SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(60*60*1000) > 24 AND (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) <= 2 THEN 1 ELSE 0 END ) '1-2 days',

SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) > 2 AND (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) <= 5 THEN 1 ELSE 0 END ) '2-5 days',
SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) > 5 AND (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) <= 10 THEN 1 ELSE 0 END ) '5-10 days',
SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) > 10 AND (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) <= 20 THEN 1 ELSE 0 END ) '10-20 days',
SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) > 20 AND (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) <= 30 THEN 1 ELSE 0 END ) '20-30 days',
SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) > 30 AND (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) <= 45 THEN 1 ELSE 0 END ) '30-45 days',
SUM (CASE WHEN (WorkOrderOperation.OPERATIONTIME - WorkOrder.CREATEDTIME)/(24*60*60*1000) >45 THEN 1 ELSE 0 END) '45+ days' FROM WorkOrder

INNER JOIN
(
SELECT
WorkOrderHistory.WORKORDERID,
MAX(WorkOrderHistory.OPERATIONTIME) 'OPERATIONTIME'
FROM WorkOrderHistory
WHERE
WorkOrderHistory.OPERATION IN ('CLOSE','RESOLVED')
AND
WorkOrderHistory.WORKORDERID IN (
SELECT
WorkOrderStates.WORKORDERID
FROM WorkOrderStates
LEFT JOIN StatusDefinition ON WorkOrderStates.STATUSID=StatusDefinition.STATUSID
WHERE StatusDefinition.INTERNALNAME IN ('Closed', 'Resolved')
)
GROUP BY WorkOrderHistory.WORKORDERID
) AS WorkOrderOperation ON WorkOrder.WORKORDERID = WorkOrderOperation.WORKORDERID

LEFT JOIN WorkOrderHistory ON
WorkOrder.WORKORDERID = WorkOrderHistory.WORKORDERID
AND
WorkOrderOperation.OPERATIONTIME = WorkOrderHistory.OPERATIONTIME

LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID = WorkOrderStates.WORKORDERID

LEFT JOIN ImpactDefinition AS Impact ON WorkOrderStates.OWNERID = Impact.IMPACTID

WHERE
WorkOrderStates.OWNERID != WorkOrderHistory.OPERATIONOWNERID
AND
WorkOrder.CREATEDTIME > (cast(DATEDIFF(s, '19700101', '2011-10-01 00:00:00') as float)*1000)
AND
WorkOrder.CREATEDTIME < (cast(DATEDIFF(s, '19700101', '2011-11-04 00:00:00') as float)*1000)

GROUP BY Impact.NAME