Query to show project tasks status wise count

Query to show project tasks status wise count


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
          • Related Articles

          • 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 view task details with account

            For Postgres and MS SQL SELECT org_name "Account", "taskmilestone"."PROJECTID" AS "Project id", "taskdet"."TASKID" AS "Task ID", "taskdet"."TITLE" AS "Title", "taskcreatedby"."FIRST_NAME" AS "Created By", "taskowner"."FIRST_NAME" AS "Owner", ...
          • Query report to show Open requests without open tasks

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...
          • Task related reports

            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",  taskdesc.DESCRIPTION AS ...
          • Query to show Request and associated task details

            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", icd.NAME AS "Item", ti.FIRST_NAME AS "Technician", ...