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>