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 Schedule start",
longtodate(projectdet.SCHEDULEDENDTIME) "Project Schedule end",
longtodate(projectdet.ACTUALSTARTTIME) "Project Actual start",
longtodate(projectdet.ACTUALENDTIME) "Project Actual end",
ProjectEstimations.ESTIMATEDCOST "Project Estimated Cost",
ProjectEstimations.tot_taskhours_cost "Project Actual Cost",
ProjectEstimations.Estimatedhours "Project Estimated hours",
ProjectEstimations.Actualtaskhours "Project Actual hours",
md.Milestoneid "Milestone ID",
md.TITLE "Milestone Title",
mdon.FIRST_NAME "Milestone Owner",
mst.StatusName "Milestone Status",
longtodate(md.SCHEDULEDSTARTTIME) "Milestone Schedule start",
longtodate(md.SCHEDULEDENDTIME) "Milestone Schedule end",
longtodate(md.ACTUALSTARTTIME) "Milestone Actual start",
longtodate(md.ACTUALENDTIME) "Milestone Actual end",
md.Estimatedhours "Milestone Estimated hours",
md.Actualtaskhours "Milestone Actual hours",
taskdet.TASKID "Task ID",
taskdet.TITLE "Task Title",
taskowner.FIRST_NAME "Task Owner",
tst.StatusName "Task Status",
taskdet.PER_OF_COMPLETION "Task Percentage Of Completion",
LONGTODATE(taskdet.SCHEDULEDSTARTTIME) "Task Scheduled Start Time",
LONGTODATE(taskdet.SCHEDULEDENDTIME) "Task Scheduled End Time",
LONGTODATE(taskdet.ACTUALSTARTTIME) "Task Actual Start Time",
LONGTODATE(taskdet.ACTUALENDTIME) "Task Actual End Time",
taskdet.Estimatedeffort "Task Estimated Minutes",
taskdet.Addtional_cost "Task Additional Costs" FROM TaskDetails taskdet
LEFT JOIN TaskToProjects projtotaskdet ON taskdet.TASKID=projtotaskdet.TASKID
LEFT JOIN ProjectDetails projectdet ON projtotaskdet.PROJECTID=projectdet.PROJECTID
LEFT JOIN TaskToProjects miletotaskdet ON taskdet.TASKID=miletotaskdet.TASKID
LEFT JOIN MileStoneDetails md ON miletotaskdet.MILESTONEID=md.MILESTONEID
LEFT JOIN SDUser projectownerdet ON projectdet.OWNERID=projectownerdet.USERID
LEFT JOIN AaaUser projectowner ON projectownerdet.USERID=projectowner.USER_ID
LEFT JOIN ProjectStatus projectstatus ON projectdet.STATUSID=projectstatus.STATUSID
LEFT JOIN SDUSer mdo ON md.OWNERID=mdo.USERID
LEFT JOIN AaaUser mdon ON mdo.USERID=mdon.USER_ID
LEFT JOIN ProjectStatus mst ON md.Statusid=mst.STATUSID
LEFT JOIN StatusDefinition tst ON taskdet.Statusid = tst.STATUSID
LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID
LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID
LEFT JOIN TaskToCharge ttc ON taskdet.TASKID=ttc.TASKID
LEFT JOIN ChargesTable ct ON ttc.CHARGEID=ct.CHARGEID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN ProjectEstimations ON projectdet.PROJECTID=ProjectEstimations.PROJECTID
New to ADSelfService Plus?
Related Articles
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 ...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. ...Project Time spent
MSSQL SELECT pr.ProjectID "Project ID", pr.TITLE "Project Title", taskdet.TaskID "TaskID", taskdet.TITLE "Title", tkd.description "Task Description", taskowner.FIRST_NAME "Owner", dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ...Project Time Spent (Query)
Please execute the below query to fetch project timespent. SELECT pr.PROJECTID "Project Id", pr.TITLE "Project Title", (TRUNC((sum(ct.TIMESPENT)/1000/3600))||' hr '||ROUND(((sum(ct.TIMESPENT)/1000)/60)%60)||' min') "TimeSpent" FROM ProjectDetails pr ...Script to send notifications to project owner and other users regarding the project closure.
Tested in Latest Build: 14306 Use case: While closing a project in addition to the project owner other users who are not involved in the project can be notified using the below attached Script. Please follow the below steps to achieve the above: 1. ...