Query to show project tasks status wise count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306)
PGSQL:
SELECT projectdet.title AS "Project name",
count(case when (taskstatus.statusid='1') THEN 1 ELSE NULL END) "Open Tasks",
count(case when (taskstatus.statusid='6') THEN 6 ELSE NULL END) "In Progress Tasks",
count(case when (taskstatus.statusid='2') THEN 2 ELSE NULL END) "On Hold Tasks",
count(case when (taskstatus.statusid='3') THEN 3 ELSE NULL END) "Closed Tasks",
count(case when (taskstatus.statusid='4') THEN 4 ELSE NULL END) "Resolved Tasks",
count(taskdet.TASKID ) "Total" FROM ProjectDetails projectdet
LEFT JOIN TaskToProjects projtotaskdet ON projectdet.PROJECTID= projtotaskdet.PROJECTID
LEFT JOIN TaskDetails taskdet ON projtotaskdet.TASKID=taskdet.TASKID
LEFT JOIN TaskToProjects miletotaskdet ON taskdet.TASKID=miletotaskdet.TASKID
LEFT JOIN MileStoneDetails md ON miletotaskdet.MILESTONEID=md.MILESTONEID
LEFT JOIN StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID
WHERE taskdet.MODULE IN ('Milestone', 'Project') group by projectdet.title
New to ADSelfService Plus?
Related Articles
Project,Milestone and Tasks.
DB Compatability: PgSQL and MSSQL SELECT projectdet .PROJECTID "Project Id", projectdet.TITLE "Project Title", projectstatus.STATUSNAME "Project Status", projectowner.FIRST_NAME "Project Owner", longtodate(projectdet.SCHEDULEDSTARTTIME) "Project ...Query to show tasks created per template along with status (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Since Task template is not stored in Task details, we cannot show count based on task templates. However, we have tried to form a query with task subject considering that, when task templates are ...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 ...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 Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...