Query to get WorkLog timespent For Request and Project

Query to get WorkLog timespent For Request and Project


Version:10500
DB:PGSQL

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

              New to ADManager Plus?

                New to ADSelfService Plus?