Report on Due date Changes
Database : MYSQL/MSSQL/PGSQL
Query
select wo.WORKORDERID "Request ID",TITLE "Title", longtodate(wo.CREATEDTIME) "Created Time",longtodate(wo.COMPLETEDTIME) "Completed Time", MODENAME "Mode", STATUSNAME "Status", aau.FIRST_NAME "Technician", longtodate(woh.OPERATIONTIME) "Operation Time",woh.OPERATION,aau1.FIRST_NAME "Updated By" from WorkOrder wo LEFT JOIN ModeDefinition md ON wo.MODEID = md.MODEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID LEFT JOIN StatusDefinition sd on wos.STATUSID = sd.STATUSID LEFT JOIN SDUser sdu on wos.OWNERID = sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID LEFT JOIN SDUser sdu1 ON sdu1.USERID = woh.OPERATIONOWNERID LEFT JOIN AaaUser aau1 ON sdu1.USERID = aau1.USER_ID LEFT JOIN WorkOrderHistoryDiff whd ON whd.HISTORYID=woh.HISTORYID where OPERATION like ('%PDATE') and whd.COLUMNNAME = 'DUEBYTIME'
AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
Krishna Bharat
ServiceDesk Plus - MSP Support team