Query to show hours and days since last update (MSSQL)

Query to show hours and days since last update (MSSQL)


Tested in Build MSSQL (14306)

Go to Reports-New Query Report and execute this query.

select wos.WORKORDERID "Request ID", max(accountdefinition.org_name) "Account",MAX(aau.FIRST_NAME) "Requester",MAX(wo.TITLE) "Subject", MAX(sd.statusname) "Ticket Status", max(au.FIRST_NAME) "Technician ",  LONGTODATE(MAX(wo.createdtime)) "Ticket Created Time", LONGTODATE(MAX(woh.operationtime)) 'Last Updated Time', MAX(sdo.name) 'Site Name', max(au.FIRST_NAME) "Technician ",  datediff(hh,dateadd(s,max(woh.operationtime/1000),'1970-01-01 00:00'),getdate()) "Hour Since Last Update",  datediff(dd,dateadd(s,max(operationtime/1000),'1970-01-01 00:00'),getdate()) "Days Since Last Update",  max(woh.operation) "Operation" from workorderhistory woh

inner join workorderstates wos ON woh.workorderid=wos.workorderid

inner join workorder wo ON wos.workorderid=wo.workorderid

left  join sdorganization sdo ON wo.siteid=sdo.org_id

left join aaauser au ON au.USER_ID=wos.OWNERID

left join StatusDefinition sd on wos.statusid=sd.statusid

LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID

LEFT JOIN AaaUser aau ON wo.requesterid=aau.USER_ID

LEFT JOIN sitedefinition sdef ON wo.siteid=sdef.siteid

LEFT JOIN accountsitemapping asm ON sdef.siteid=asm.siteid 

LEFT JOIN accountdefinition ON accountdefinition.org_id=asm.accountid

where sd.statusname !='Closed' and accountdefinition.org_id in ($Account) and DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.createdtime/1000),'1970-01-01 00:00:00'),GETDATE()) > 14 group by wos.WORKORDERID order by 9