Query to retrieve worklog details
Tested in: 14610 (Postgres)
Query:
SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", sdo.NAME AS "Site", ti.FIRST_NAME AS "Request Technician" ,au1.FIRST_NAME AS "Worklog Technician", TO_CHAR(((sum(ct.TIMESPENT))/1000 || ' second')::interval, 'HH24:MI:SS') AS "Time Spent", ct.TOTAL_CHARGE AS "Total Cost", LONGTODATE(ct.CREATEDTIME) AS "Created Time", LONGTODATE(ct.TS_STARTTIME) AS "Start Time", LONGTODATE(ct.TS_ENDTIME) AS "End Time", wtd.NAME AS "Work Log Type", ct.SHORT_DESCRIPTION AS "Description", ac.CONTRACTNAME AS "Contract Name",sdo1.NAME AS "Account Name" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrderToCharge wotoc on wotoc.WORKORDERID=wo.WORKORDERID LEFT JOIN ChargesTable ct ON ct.CHARGEID=wotoc.CHARGEID LEFT JOIN AaaUser au1 ON ct.TECHNICIANID=au1.USER_ID LEFT JOIN WorkLogTypeDefinition wtd ON ct.WORKLOGTYPEID=wtd.WORKLOGTYPEID LEFT JOIN WorklogtoContract wltc ON ct.CHARGEID=wltc.CHARGEID LEFT JOIN AccountContract ac ON wltc.CONTRACTID=ac.CONTRACTID LEFT JOIN WorkOrderAccountMapping woac ON woac.WORKORDERID=wo.WORKORDERID LEFT JOIN SDOrganization sdo1 ON woac.ACCOUNTID=sdo1.ORG_ID WHERE (wo.ISPARENT='1') group by wo.workorderid,aau.first_name,sdo.NAME,ti.FIRST_NAME,au1.FIRST_NAME,ct.TOTAL_CHARGE,ct.CREATEDTIME,ct.TS_STARTTIME,ct.TS_ENDTIME,wtd.NAME,ct.SHORT_DESCRIPTION,ac.CONTRACTNAME,sdo1.NAME
Result:
New to ADSelfService Plus?
Related Articles
Query to retrieve the requests details
Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...Complete worklog report -MSSQL
Query: SELECT aau.FIRST_NAME AS "Requester", max(wo.WORKORDERID) AS "Request ID", rtdef.NAME AS "Request request type", wo.TITLE AS "Subject", wo.description , std.STATUSNAME AS "Request Status", ti.FIRST_NAME AS "Assigned Technician", ...Query to retrieve the Change Details
Tested in: 14504, 14610 QUERY: SELECT chdt.CHANGEID AS "Change ID", cmDef.FIRST_NAME AS "Change Manager", ownaaa.FIRST_NAME AS "Change Owner", approvaldef.STATUSNAME AS "Approval Status", qd.QUEUENAME AS "Group", statusDef.STATUSDISPLAYNAME AS ...Query to retrieve the active and expired CPH contracts details
Tested in: 14620, 14610 and 14306 Query 1: To return the Active CPH contracts details: select ad.org_name "Account", ad.org_name "Account", sp.serviceplanname "Service Plan", ac.CONTRACTNO "Contract No", longtodate(ac.startdate) "Contract Start ...Query to retrieve Account details along with additional attributes, postal address and additional fields data
TESTED IN BUILDS: 14700 (Postgres) QUERY 1 - Without Account Additional Fields: select ad.ORG_NAME As "Account", org.description AS "DESCRIPTION", ad.LOGIN_WEBURI AS "LOGIN WEB URL", ad.LOGIN_URI AS "LOGIN URI", ad.SUPPORT_EMAIL AS "SUPPORT EMAIL", ...