Service Desk Plus MSSQL query Last Update Time - Blank Last Update Time Field
I'm writing today to see if I can get some help with a MSSQL query that is working pretty well. You guys
helped me with this awhile back. I having a bit of trouble that I'm hoping you can fix really quick.
In the query below how do I change it so that if the last update time field is blank to be the created date instead of the '19700101' I know that I'm close with dateadd create date but can't quite get the syntax right.
Thanks
Tony Blandin
SELECT ti.FIRST_NAME "Technician",
wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
cd.CATEGORYNAME "Category",
sdo.NAME "Site",
dateadd(s,wos.LAST_TECH_UPDATE/1000,'19700101') "Last Update Time"
FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE (std.ispending='1') AND datediff(dd,dateadd(s,wos.LAST_TECH_UPDATE/1000,'19700101'),getdate()) >30 AND wo.ISPARENT='1'
ORDER BY 1,6,pd.PRIORITYNAME