How can we add Criteria to View only Open, In Progress and On Hold Tasks on Change Requests?
I have prepared below Query but I can't able to add a Criteria to view only Open, In Progress and On Hold Tasks associated to the Change Request. Please anyone can help me to add the Criteria on the "Task Status".
Query :
SELECT "chdt"."CHANGEID" AS "Change ID",
"orgaaa"."FIRST_NAME" AS "Change Requester",
longtodate("chdt"."CREATEDTIME") AS "Created Time",
"stageDef"."DISPLAYNAME" AS "Stage",
"statusDef"."STATUSDISPLAYNAME" AS "Status",
rev.NAME AS "Change Implementers",
td.title "Task Title",
sd.statusname "Task Status",
own.first_name "Task Owner",
(DATEDIFF_BIG(MILLISECOND, '1970-01-01', GETDATE()) - timespent.timespent_operationtime) / 1000 / 86400 AS "Time spenton current status (Days)" FROM "ChangeDetails" "chdt"
LEFT JOIN "SDUser" "orgsd" ON "chdt"."INITIATORID"="orgsd"."USERID"
LEFT JOIN "AaaUser" "orgaaa" ON "orgsd"."USERID"="orgaaa"."USER_ID"
LEFT JOIN "SDUser" "ownsd" ON "chdt"."TECHNICIANID"="ownsd"."USERID"
LEFT JOIN aaauser req ON chdt.initiatorid = req.user_id
LEFT JOIN changetotaskdetails chgtotd ON chdt.changeid = chgtotd.changeid
LEFT JOIN taskdetails td ON chgtotd.taskid = td.taskid
LEFT JOIN aaauser own ON td.ownerid = own.user_id
LEFT JOIN (SELECT role.changeid,
Max(us.first_name) AS NAME
FROM changeroleusermapping ROLE
LEFT JOIN aaauser us
ON ROLE.userid = us.user_id
WHERE ROLE.roleid = 8
GROUP BY ROLE.changeid)rev
ON chdt.changeid = rev.changeid
LEFT JOIN (SELECT MAX(his.operationtime) AS "timespent_operationtime", his.changeid AS "timespent_changeid" FROM changehistory his LEFT JOIN changehistorydiff hisdif ON his.historyid = hisdif.historyid WHERE hisdif.columnname = 'WFSTATUSID' GROUP BY his.changeid) timespent ON chdt.changeid = timespent.timespent_changeid
LEFT JOIN "AaaUser" "ownaaa" ON "ownsd"."USERID"="ownaaa"."USER_ID"
LEFT JOIN "Change_StageDefinition" "stageDef" ON "chdt"."WFSTAGEID"="stageDef"."WFSTAGEID"
LEFT JOIN statusdefinition sd ON td.statusid = sd.statusid
LEFT JOIN "Change_StatusDefinition" "statusDef" ON "chdt"."WFSTATUSID"="statusDef"."WFSTATUSID" WHERE ( ( ( "stageDef"."DISPLAYNAME" != N'Close' ) AND ("stageDef"."DISPLAYNAME" != N'Submission' ) AND ("stageDef"."DISPLAYNAME" != N'Review' ) OR ( "stageDef"."DISPLAYNAME" IS NULL ) ) AND ( "chdt"."DELETEDTIME" IS NULL ) )
We are currently using SQL Server as a Database and our application built is 14300.