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 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 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 ...Script to send notifications to project owner when (Project is edited, task is added, Milestone is added)
Last tested in Build: 10602 1. Send mail project owner Please follow the below steps. 1.. Go to Admin --> Project Custom Functions --> Custom Actions --> Paste the content from the attachment (1. Project_sendmail.txt) and save it with a name. 2. ...