Version : 14001

DB : PGSQL

OUTPUT :

Query :

SELECT ad.org_name AS "Account Name",
pd.projectid AS "Project ID",
    pd.title AS "Title",
    LONGTODATE(pd.createdtime) AS "Project Date",
    pd.projectcode AS "Project Code",
       aa.first_name AS "Technician",
    taskdet.title AS "Task Name",
    CONCAT(COALESCE(TRUNC(sum(COALESCE(ch.timespent, 0))/3600000,0)),' Hrs ',COALESCE(MOD(TRUNC(sum(COALESCE(ch.timespent, 0))/60000,0),60),0),' Mins') AS "Time Spent",
    cmt.comment FROM
    ProjectMembers pm
    INNER JOIN ProjectDetails pd ON pm.projectid = pd.projectid
    INNER JOIN TaskToProjects ttp ON pd.projectid = ttp.projectid
    INNER JOIN TaskDetails taskdet ON ttp.taskid = taskdet.taskid
    INNER JOIN AaaUser aa ON pm.userid = aa.user_id
INNER JOIN ProjectAccMapping pam ON pd.PROJECTID=pam.PROJECTID
INNER JOIN AccountDefinition ad ON pam.ACCOUNTID=ad.ORG_ID
LEFT JOIN Projecttocomment ptc ON pd.projectid = ptc.projectid
LEFT JOIN comments cmt ON ptc.commentid = cmt.commentid
    LEFT JOIN TaskToCharge ttc ON ttp.taskid = ttc.taskid
    LEFT JOIN ChargesTable ch ON ttc.chargeid = ch.chargeid AND ch.technicianid = aa.user_id
GROUP BY
    pd.projectid,
    pd.title,
    pd.projectcode,
    aa.first_name,
    taskdet.title,ad.org_name,
cmt.comment
ORDER BY
    pd.title;