Query to get site's operational hour based report.
If ABC site's operational hour is 9AM to 6PM, When generating the query the report will give the request data that are created during the operational hours which is from 9 AM to 6 PM.
SELECT wo.WORKORDERID AS "Request ID", lvd.LEVELNAME AS "Level", pd.PRIORITYNAME AS "Priority", longtodate(wo.CREATEDTIME) AS "Created Time", longtodate(wo.RESPONDEDTIME) AS "Responded Date", longtodate(ct.TS_STARTTIME) AS "Time Spent Starttime", wo.TIMESPENTONREQ AS "Time Elapsed", longtodate(wo.COMPLETEDTIME) AS "Completed Time", rctd.FIRST_NAME AS "Time Spent Technician", longtodate(wo.RESOLVEDTIME) AS "Resolved Time", aau.FIRST_NAME AS "Requester", std.STATUSNAME AS "Request Status" FROM WorkOrder wo LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID LEFT JOIN PortalAccounts port_acc ON wam.ACCOUNTID=port_acc.ACCOUNTID LEFT JOIN AccountDefinition ad ON port_acc.ACCOUNTID=ad.ORG_ID 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 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 StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN LevelDefinition lvd ON wos.LEVELID=lvd.LEVELID LEFT JOIN OperationalHoursDef ohd ON wo.SITEID = ohd.SITEID LEFT JOIN SDOrganization sdo ON wo.SITEID = sdo.ORG_ID WHERE ( ( ( EXTRACT( HOUR FROM TO_TIMESTAMP(wo.CREATEDTIME / 1000) ) >= EXTRACT( HOUR FROM CAST(ohd.starttime AS TIME) ) ) AND ( (wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL) ) ) AND ( ( EXTRACT( HOUR FROM TO_TIMESTAMP(wo.CREATEDTIME / 1000) ) <= EXTRACT( HOUR FROM CAST(ohd.ENDTIME AS TIME) ) ) AND ( ( (wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL) ) AND (wo.CREATEDTIME != -1) ) ) ) AND wo.ISPARENT = '1' AND ad.ORG_NAME = 'ACCOUNTNAME' AND sdo.NAME = 'SITENAME'
New to ADSelfService Plus?
Related Articles
Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...Technician notification based on the site
This KB will help you to get the notification about based on the site to a technician. Please follow the below instructions. Please navigate to Admin >> Request timer action >> Add new timer action. Please configure the below in the application. ...Tickets created outside operational hours( MSSQL)
Tested in Build MSSQL (14306) MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...Operational Hours and Differential Hours while adding a worklog
Under Self-Service portal settings, when you have the option "Show in work log differential hours based on SLA configuration" set to "Yes", the application will look for the SLA configuration and show the operational hours accordingly while adding a ...Query to show Site and its related settings(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute the query. select adef.org_name "Account Name",sdef.siteid "Site ID", sdo.name "Site Name", siteconf.settingdetail "Related Settings", CASE WHEN ...