SELECT ad.ORG_NAME AS "Account",
COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID",
to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'Request' "Module",
CONCAT(COALESCE(TRUNC(sum(ct.TIMESPENT)/3600000,0)),' Hrs ',COALESCE(MOD(TRUNC(sum(ct.TIMESPENT)/60000,0),60),0),' Mins') "Time Spent",
rctd.FIRST_NAME "Technician" FROM ChargesTable ct
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID
LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID
LEFT JOIN workorderstates wos ON wos.workorderid=wo.workorderid
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN WorkorderToTaskDetails wtk ON tk.TASKID=wtk.TASKID
LEFT JOIN Workorder wo2 ON wtk.WORKORDERID=wo2.WORKORDERID
LEFT JOIN SDUser rcti ON wos.ownerid=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
LEFT JOIN NoBillRequestCharges nbrc ON ct.CHARGEID=nbrc.requestchargeid
WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) AND nbrc.isbillableworklog = true GROUP BY COALESCE(wtk.WORKORDERID , wo.workorderid), rctd.FIRST_NAME,ct.CREATEDTIME,ad.ORG_NAME
UNION
SELECT ad.ORG_NAME AS "Account",
tpr.PROJECTID "Module ID",
to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",
'Project' "Module" ,
CONCAT(COALESCE(TRUNC(sum(ct.TIMESPENT)/3600000,0)),' Hrs ',COALESCE(MOD(TRUNC(sum(ct.TIMESPENT)/60000,0),60),0),' Mins') "Time Spent",
rctd.FIRST_NAME "Technician" FROM ChargesTable ct
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN TaskTOProjects tpr ON tk.taskid=tpr.taskid
LEFT JOIN ProjectDetails pr ON tpr.PROJECTID=pr.PROJECTID
LEFT JOIN SDUser rcti ON pr.ownerid=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN ProjectAccMapping pam ON pr.projectid=pam.projectid
LEFT JOIN AccountDefinition ad ON pam.accountid=ad.org_id
LEFT JOIN NoBillRequestCharges nbrc ON ct.CHARGEID=nbrc.requestchargeid
WHERE tk.MODULE IN ('project', 'milestone') AND nbrc.isbillableworklog = true GROUP BY tpr.PROJECTID,rctd.FIRST_NAME,ct.CREATEDTIME,ad.ORG_NAME ORDER BY 1
Output :
Thanks & Regards
R.Abdul Samad
Query Specialist
SDP MSP Team