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
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 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", ...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", ...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 ...