Query to show status changes of a task
Working on Builds: 14500
PGSQL & MSSQL:
SELECT taskdet.taskid "Task ID",
taskdet.TITLE "Task Title",
std.STATUSNAME "Task Status",
aau.first_name "Task Owner",
aau1.first_name "Status changed by",
LONGTODATE(tdh.operationtime) "Status changed in (Time)",
std1.STATUSNAME "Status Changed From",
std2.STATUSNAME "Status Changed To" FROM TaskDetails taskdet
LEFT JOIN StatusDefinition std ON taskdet.STATUSID=std.STATUSID
LEFT JOIN TaskdetailsHistory tdh ON taskdet.taskID = tdh.taskid
LEFT JOIN TaskdetailsHistoryDiff tdhd ON tdh.HISTORYID = tdhd.HISTORYID
left join sitedefinition sdef ON taskdet.siteid=sdef.siteid
left join accountsitemapping asm ON sdef.siteid=asm.siteid
left join accountdefinition ON accountdefinition.org_id=asm.accountid
LEFT JOIN SDUser sdu ON taskdet.ownerid=sdu.userid
LEFT JOIN AaaUser aau ON aau.user_id=sdu.userid
LEFT JOIN SDuser sdu1 ON tdh.operationownerid=sdu1.userid
LEFT JOIN AaaUser aau1 ON aau1.user_id=sdu1.userid
LEFT JOIN StatusDefinition std1 ON tdhd.prev_value =std1.STATUSNAME
LEFT JOIN StatusDefinition std2 ON tdhd.current_value =std2.STATUSNAME
WHERE tdhd.COLUMNNAME IN ('STATUSID') AND taskdet.createddate>=<from_lastmonth> and taskdet.createddate<=<to_lastmonth>
ORDER BY 1, OPERATIONTIME DESC
NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.
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>
New to ADSelfService Plus?
Related Articles
Task related reports (MSSQL)
Tested in MSSQL build (14306) Sample Output and headers present in this report Goto reports -> New query report and executes the following SELECT taskdet.TASKID AS "Task ID", taskdet.MODULE AS "Module", taskprior.PRIORITYNAME AS "Priority", ...Query to show priority changes in the ticket (MSSQL)
Tested in MSSQL build (14306) This query shows which tickets were downgraded from P1. MSSQL: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.DUEBYTIME) "DueBy ...Query to show Request and associated task details (PGSQL & MSSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", ...Query to show last comments added in Projects task_MSSQL
SELECT taskdet.TASKID AS "Task ID", taskdet.TITLE AS "Title", taskowner.FIRST_NAME AS "Owner", taskcreatedby.FIRST_NAME AS "Created By", taskstatus.STATUSNAME AS "Task Status", taskdesc.DESCRIPTION AS "Description", ...Query to show status changes in a ticket_ PGSQL
Tested in Build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request Status", wo.TITLE "Subject", woh.OPERATION "Operation", LONGTODATE(wo.CREATEDTIME) CREATEDTIME, aau1.FIRST_NAME PERFORMEDBY, ...