Query for Billable and Non Billable worklogs of a request (PGSQL)
Tested in Build PGSQL (14300)
Non Billable Requests pgsql
SELECT wo.workorderid "Request ID", wo.TITLE AS "Subject", longtodate(ct.CREATEDTIME) AS "Time Spent Created Time", rctd.FIRST_NAME AS "Time Spent Technician", TO_CHAR((sum(ct.TIMESPENT)/1000 || ' second')::interval, 'HH24:MI:SS') "TimeSpent" FROM WorkOrder wo LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id INNER JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN NonBillableRequests nbr ON wo.workorderid=nbr.requestid LEFT JOIN nobillrequestcharges NBRC ON ct.chargeid=nbrc.requestchargeid WHERE ct.createdtime>=DATETOLONG('2015-01-03 07:00:00') and ct.createdtime<=DATETOLONG('2018-01-03 16:00:00') and ad.ORG_NAME = 'PINNACLE' AND nbrc.requestchargeid is not null group by 1,3,4 order by 1
Billable Requests pgsql
SELECT wotoc.workorderid "Request ID", wo.TITLE AS "Subject", longtodate(ct.CREATEDTIME) AS "Time Spent Created Time", rctd.FIRST_NAME AS "Time Spent Technician", TO_CHAR((sum(ct.TIMESPENT)/1000 || ' second')::interval, 'HH24:MI:SS') "TimeSpent" FROM WorkOrder wo LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID LEFT JOIN worklogcharges wc ON wotoc.CHARGEID=wc.requestchargeid LEFT JOIN ChargesTable ct ON ct.chargeid=wc.requestchargeid LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id INNER JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID WHERE ct.createdtime>=DATETOLONG('2015-01-03 07:00:00') and ct.createdtime<=DATETOLONG('2018-01-03 16:00:00') and ct.chargeid=wc.requestchargeID and ad.ORG_NAME = 'PINNACLE' group by 1,2,3,4 order by 1
New to ADSelfService Plus?
Related Articles
Mark Worklogs as Non-Billable by default for 10500 and above
Please follow the below mentioned to mark the Worklogs as non-billable by default. This is applicable for both Requests and Changes Module 1. Stop the application. 2. Please take a trimmed backup of the application <OR> take a Snapshot of both the ...Mark Request as Non-Billable
You can make use of Business Rules to set the Criteria as "Request Type is" (or any Criteria as per yours) and under Actions set "Mark Request as Non-Billable". So once the Request is marked as Non-Billable, the worklog added under that request will ...Query for request attachment details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician", sa.ATTACHMENTNAME "Attachment ...Query to retrieve the requests that are created and completed within the given time frame
Tested in: 14610 & 14301 The following queries will return the IDs and counts of requests created within a given time frame and completed (Resolved, Closed, Cancelled) within the same time frame. QUERY FOR INCIDENT REQUESTS: SELECT ...Query to show shared requests (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Shared to Tech: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ...