Worklog Queries for various modules

Worklog Queries for various modules

Worklog Queries

Database : PGSQL

Request Charges

SELECT COALESCE(wtk.WORKORDERID , wo.workorderid) "Request ID", CASE WHEN (tk.TASKID) IS NOT NULL THEN (wo2.TITLE) ELSE (wo.TITLE) END "Title",cast((ct.TIMESPENT/1000 * interval '1 second') as varchar) "Time Spent",to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time", rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct 
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID 
LEFT JOIN WorkOrder wo ON wotoc.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 ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-01 00:00:00') * 1000 AS BIGINT)  AND  ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-30 00:00:00') * 1000 AS BIGINT) 
order by 1,5

Problem

SELECT COALESCE(ptk.PROBLEMID , pb.PROBLEMID) "Problem ID",CASE WHEN (tk.TASKID) IS NOT NULL THEN (pb2.TITLE) ELSE (pb.TITLE) END "Title",  cast((ct.TIMESPENT/1000 * interval '1 second') as varchar) "Time Spent",to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time" ,rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct LEFT JOIN ProblemToCharge pbtoc ON ct.CHARGEID=pbtoc.CHARGEID 
LEFT JOIN Problem pb ON pbtoc.PROBLEMID=pb.PROBLEMID 
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID 
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID 
LEFT JOIN ProblemToTaskDetails ptk ON tk.TASKID=ptk.TASKID 
LEFT JOIN Problem pb2 ON ptk.PROBLEMID=pb2.PROBLEMID 
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
WHERE (ptk.PROBLEMID IS NOT NULL OR pb.PROBLEMID IS NOT NULL) AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-01 00:00:00') * 1000 AS BIGINT)  AND  ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-30 00:00:00') * 1000 AS BIGINT)
order by 1,5

Change

SELECT COALESCE(ctk.CHANGEID , ch.CHANGEID) "Change ID",CASE WHEN (tk.TASKID) IS NOT NULL THEN (ch2.TITLE) ELSE (ch.TITLE) END "Title",  cast((ct.TIMESPENT/1000 * interval '1 second') as varchar)  "Time Spent", to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time" ,rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct 
LEFT JOIN ChangeToCharge chtoc ON ct.CHARGEID=chtoc.CHARGEID 
LEFT JOIN ChangeDetails ch ON chtoc.CHANGEID=ch.CHANGEID 
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID 
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID 
LEFT JOIN ChangeToTaskDetails ctk ON tk.TASKID=ctk.TASKID 
LEFT JOIN ChangeDetails ch2 ON ctk.CHANGEID=ch2.CHANGEID 
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-01 00:00:00') * 1000 AS BIGINT)  AND  ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-30 00:00:00') * 1000 AS BIGINT)
order by 1,5

Project 

SELECT tpr.PROJECTID "Project ID" , (pr.TITLE) "Title",  
cast((ct.TIMESPENT/1000 * interval '1 second') as varchar) "Time Spent",
to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time" ,rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct 
INNER 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 ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID WHERE  tk.MODULE IN ('project', 'milestone') AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-01 00:00:00') * 1000 AS BIGINT)  AND  ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2015-06-30 00:00:00') * 1000 AS BIGINT)
order by 1,5

Krishna Bharat

ServiceDesk Plus - MSP team

      New to ADSelfService Plus?

        Resources

                Related Products