Working on Build's: 14500
PGSQL:
SELECT ad.ORG_NAME AS "Account",
wo.WORKORDERID AS "Request ID",
wos.ISOVERDUE AS "Overdue Status",
ad.ORG_NAME AS "Account",
CAST (DATE_PART('day', now() - FROM_UNIXTIME(wo.DUEBYTIME /1000)) AS varchar)"Days since violated" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
WHERE (wo.ISPARENT='1' and wos.ISOVERDUE = 'true')
ORDER BY 1
MSSQL:
SELECT wo.workorderid "Request ID",
wotodesc.FULLDESCRIPTION AS "Description",
wos.ISOVERDUE AS "Overdue Status",
longtodate(wo.DUEBYTIME) AS "DueBy Time",
DATEDIFF (day, DATEADD(s, wo.DUEBYTIME/1000, '01-01-1970 00:00:00'), getDate()) "Time Delay in Days",
dpt.DEPTNAME AS "Department",
ti.FIRST_NAME AS "Technician" FROM WorkOrder wo
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1' and wos.ISOVERDUE = 'true')
GROUP BY wo.workorderid,wotodesc.FULLDESCRIPTION,wos.ISOVERDUE,wo.DUEBYTIME,dpt.DEPTNAME,ti.FIRST_NAME
Sample Table