Version : 13000

DB : PGSQL

OUTPUT:

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",
worktype.name AS "WorkLog Type",
rctd.FIRST_NAME AS "Time sheet Technician",
ct.DESCRIPTION AS "Time Spent DESCRIPTION",
longtodate(ct.TS_STARTTIME) AS "Time Spent STARTTIME",
longtodate(ct.TS_ENDTIME) AS "Time Spent ENDTIME",
ct.TOTAL_CHARGE AS "TOTAL CHARGE",
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",
TO_CHAR(((sum(ct.TIMESPENT))/1000 || ' second')::interval, 'HH24:MI:SS') "Time Spent",
longtodate(ct.CREATEDTIME) "Time Spent 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 SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN worklogtypedefinition worktype ON ct.worklogtypeid=worktype.worklogtypeid
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,worktype.name,rctd.FIRST_NAME,ct.DESCRIPTION,ct.TS_STARTTIME,ct.TS_ENDTIME,ct.TOTAL_CHARGE