Project Report - Revision
A while back, you gave me a query for a formatted Project Report. I need something very similar, but I need to show all statuses for Tasks, Milestones and Projects; the one that was given to me currently only shows me Open/Held ones.
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",
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",
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" 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 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 ProjectStatus 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 ProjectEstimations ON projectdet.PROJECTID=ProjectEstimations.PROJECTID order by 1
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",
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",
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" 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 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 ProjectStatus 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 ProjectEstimations ON projectdet.PROJECTID=ProjectEstimations.PROJECTID order by 1