Query to show tickets that are in resolved status for more than 1 hour ( MSSQL)

Query to show tickets that are in resolved status for more than 1 hour ( MSSQL)

Tested in build MSSQL (14306)

MSSQL:

SELECT wo.WORKORDERID AS "Request ID",ad.ORG_NAME AS "Account" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID INNER JOIN Accountsitemapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID LEFT JOIN StatusDefinition std1 ON cast(cast(wohd.prev_value AS varchar) AS int) =std1.STATUSID LEFT JOIN StatusDefinition std2 ON cast(cast(wohd.current_value AS varchar) AS int) = std2.STATUSID LEFT JOIN Sduser sdu ON wo.requesterid=sdu.userid LEFT JOIN aaauser aau ON sdu.userid=aau.user_id WHERE  ((( OPERATIONTIME >= <from_thisyear> ) AND ( OPERATIONTIME <= <to_thisyear> ))) AND ((DATEDIFF(day, DATEADD(s, wo.RESOLVEDtime/1000, '01-01-1970 00:00:00'), getDate()) > 1)) AND wo.ISPARENT='1' and wohd.COLUMNNAME IN ('STATUSID')  and std.STATUSNAME = 'Resolved'  AND wo.title='Subject' AND aau.first_name='Guest' group by wo.WORKORDERID,ad.ORG_NAME
                New to ADManager Plus?

                  New to ADSelfService Plus?