Version : 10602

DB: MSSQL

Output :

Query :

      SELECT "projectdet"."PROJECTID" AS "Project id",
 "projectdet"."TITLE" AS "Project NAME",
"ad"."ORG_NAME" AS "Account",
"projectestimations"."TOT_TASKHOURS_COST" AS "Actual Cost",
"projectdet"."ACTUALENDTIME" AS "Actual end",
"projectestimations"."ACTUALTASKHOURS" AS "Actual Hours",
"projectdet"."ACTUALSTARTTIME" AS "Actual start",
"projectdet"."CREATEDTIME" AS "Project Created date",
"projectcreatedby"."FIRST_NAME" AS "Createdby",
"deptdef"."DEPTNAME" AS "Department",
"projectestimations"."ESTIMATEDCOST" AS "Estimated cost",
"projectestimations"."ESTIMATEDHOURS" AS "Estimated Hours",
"projectowner"."FIRST_NAME" AS "Owner",
"projectprior"."PRIORITYNAME" AS "Priority",
"projectdet"."PROJECTCODE" AS "Project Code",
"projectdesc"."DESCRIPTION" AS "Project Description",
"projecttypedef"."PROJECTTYPENAME" AS "Project Type",
"projectdet"."PROJECTEDEND" AS "Projected On",
"projectrequester"."FIRST_NAME" AS "Requested By",
"projectdet"."SCHEDULEDENDTIME" AS "Schedule end",
"projectdet"."SCHEDULEDSTARTTIME" AS "Schedule start",
"projectsitename"."NAME" AS "Site",
"projectstatus"."STATUSNAME" AS "Status",
cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60) as varchar(20))+ 'Secs' "Time Spent",
longtodate(ct.CREATEDTIME) "Log date",
propmau.FIRST_NAME "Project Member",
AaaLogin.name "User Name"  FROM "ProjectDetails" "projectdet"
LEFT JOIN TaskToProjects projtotaskdet ON projectdet.projectid=projtotaskdet.projectid
LEFT JOIN TaskDetails taskdet ON taskdet.TASKID=projtotaskdet.TASKID
LEFT JOIN TaskToCharge tasktoch ON taskdet.TASKID=tasktoch.TASKID
LEFT JOIN chargestable ct ON ct.CHARGEID=tasktoch.CHARGEID
LEFT JOIN "ProjectTypeDefinition" "projecttypedef" ON "projectdet"."PROJECTTYPEID"="projecttypedef"."PROJECTTYPEID"
LEFT JOIN projectmembers propm ON projectdet.projectid=propm.projectid
LEFT JOIN "SDUser" propmsd ON "propm"."USERID"="propmsd"."USERID"
LEFT JOIN "AaaUser" propmau ON "propmsd"."USERID"="propmau"."USER_ID"
LEFT JOIN AaaLogin ON propmau.USER_ID=AaaLogin.USER_ID
LEFT JOIN "SDUser" "projectownerdet" ON "projectdet"."OWNERID"="projectownerdet"."USERID"
LEFT JOIN "AaaUser" "projectowner" ON "projectownerdet"."USERID"="projectowner"."USER_ID"
LEFT JOIN "PriorityDefinition" "projectprior" ON "projectdet"."PRIORITYID"="projectprior"."PRIORITYID"
LEFT JOIN "ProjectStatus" "projectstatus" ON "projectdet"."STATUSID"="projectstatus"."STATUSID"
LEFT JOIN "SDUser" "projectcreatedbydet" ON "projectdet"."CREATEDBY"="projectcreatedbydet"."USERID"
LEFT JOIN "AaaUser" "projectcreatedby" ON "projectcreatedbydet"."USERID"="projectcreatedby"."USER_ID"
INNER JOIN "ProjectAccMapping" "pam" ON "projectdet"."PROJECTID"="pam"."PROJECTID"
INNER JOIN "AccountDefinition" "ad" ON "pam"."ACCOUNTID"="ad"."ORG_ID"
LEFT JOIN "SiteDefinition" "projectsite" ON "projectdet"."SITEID"="projectsite"."SITEID"
LEFT JOIN "SDOrganization" "projectsitename" ON "projectsite"."SITEID"="projectsitename"."ORG_ID"
LEFT JOIN "ProjectEstimations" "projectestimations" ON "projectdet"."PROJECTID"="projectestimations"."PROJECTID" LEFT JOIN "ProjectDescription" "projectdesc" ON "projectdet"."PROJECTID"="projectdesc"."PROJECTID"
LEFT JOIN "SDUser" "projectrequesterdet" ON "projectdet"."REQUESTEDBY"="projectrequesterdet"."USERID"
LEFT JOIN "AaaUser" "projectrequester" ON "projectrequesterdet"."USERID"="projectrequester"."USER_ID"
LEFT JOIN "DepartmentDefinition" "deptdef" ON "projectdet"."DEPARTMENTID"="deptdef"."DEPTID"
LEFT JOIN "Project_Fields" "projectfields" ON "projectdet"."PROJECTID"="projectfields"."PROJECTID"
INNER JOIN "ProjectAccMapping" ON "projectdet"."PROJECTID"="ProjectAccMapping"."PROJECTID"  group by projectdet.PROJECTID,"projectdet"."TITLE","ad"."ORG_NAME","projectestimations"."TOT_TASKHOURS_COST","projectdet"."ACTUALENDTIME","projectdet"."ACTUALENDTIME","projectestimations"."ACTUALTASKHOURS","projectdet"."ACTUALSTARTTIME","projectdet"."CREATEDTIME","projectcreatedby"."FIRST_NAME","deptdef"."DEPTNAME","projectestimations"."ESTIMATEDCOST","projectestimations"."ESTIMATEDHOURS","projectowner"."FIRST_NAME","projectprior"."PRIORITYNAME","projectdet"."PROJECTCODE","projectdesc"."DESCRIPTION","projecttypedef"."PROJECTTYPENAME","projectdet"."PROJECTEDEND","projectrequester"."FIRST_NAME","projectdet"."SCHEDULEDENDTIME","projectdet"."SCHEDULEDSTARTTIME","projectsitename"."NAME","projectstatus"."STATUSNAME",AaaLogin.name,propmau.FIRST_NAME,ct.CREATEDTIME

Thanks & Regards

R.Abdul Samad

Query Specialist

SDP MSP Team