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()) + (taskdet.SCHEDULEDSTARTTIME/1000), '1970-01-01 00:00:00') "Scheduled Start Time",
dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (taskdet.SCHEDULEDENDTIME/1000), '1970-01-01 00:00:00') "Scheduled End Time",
dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (taskdet.ACTUALSTARTTIME/1000), '1970-01-01 00:00:00') "Actual Start Time",
dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (taskdet.ACTUALENDTIME/1000), '1970-01-01 00:00:00') "Actual End Time",
taskstatus.STATUSNAME "Task Status",
CAST(ct.TIMESPENT AS FLOAT)/1000/3600 "TimeSpent",
tt.FIRST_NAME "Time Spent Tech" FROM ProjectDetails pr
LEFT JOIN TaskToProjects tpr ON pr.PROJECTID= tpr.PROJECTID
LEFT JOIN TaskDetails taskdet ON tpr.TASKID=taskdet.TASKID
LEFT JOIN TaskDescription tkd ON taskdet.TASKID=tkd.TASKID
LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID
LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID
LEFT JOIN StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID
LEFT JOIN TaskToCharge tkc ON taskdet.TASKID=tkc.TASKID
LEFT JOIN ChargesTable ct ON tkc.CHARGEID=ct.CHARGEID
LEFT JOIN AaaUser tt ON ct.TECHNICIANID=tt.USER_ID
ORDER BY 2,
1
PGSQL
SELECT pr.TITLE "Project Title",
taskdet.TITLE "Task Title",
taskowner.FIRST_NAME "Task Owner",
ct.TIMESPENT /1000/3600 "TimeSpent hrs",
ctt.FIRST_NAME "Time Spent Tech" FROM ProjectDetails pr
LEFT JOIN TaskToProjects tpr ON pr.PROJECTID= tpr.PROJECTID
LEFT JOIN TaskDetails taskdet ON tpr.TASKID=taskdet.TASKID
LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID
LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID
LEFT JOIN TaskToCharge tkc ON taskdet.TASKID=tkc.TASKID
LEFT JOIN ChargesTable ct ON tkc.CHARGEID=ct.CHARGEID
LEFT JOIN AaaUser ctt ON ct.TECHNICIANID=ctt.USER_ID
ORDER BY 1