Query to list the Allowance, used and remaining Hours(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
select ad.org_name "Account",sp.serviceplanname "Service Plan",longtodate(ac.startdate) "Contract Start Date", longtodate(ac.expirydate) "Contract Expiry Date", sp.fixedmonthlyunits "Total Allowance (Hours)", (MAX(bc.consumedunits)/3600000) "Hours Used",MAX(sp.fixedmonthlyunits)-(MAX(bc.consumedunits)/3600000) "Hours Remaining" from workorder wo LEFT JOIN workorderstates wos on wo.workorderid = wos.workorderid
LEFT JOIN statusdefinition sd on sd.statusid = wos.statusid
left join accountsitemapping asm on asm.siteid = wo.siteid
inner join accountcontract ac on ac.accountid = asm.accountid
left join serviceplan sp ON ac.serviceplanid=sp.serviceplanid
left join billcycle bc ON ac.contractid=bc.contractid
Left join accountdefinition ad on asm.accountid = ad.org_id
where ac.isactivecontract ='true' and plantype='Charge Per Hour' and ac.accountid in ($Account)
group by ad.org_name,ac.startdate,ac.expirydate,sp.serviceplanname,sp.FIXEDMONTHLYUNITS
New to ADSelfService Plus?
Related Articles
Query to find the Consumed Units (Charge by Requests / Hour) (PGSQL & MSSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports Tab >> New Query Report >> Run the below query Consumed units (Charge by Requests) : PGSQL & MSSQL: select ad.org_name "Account",sp.serviceplanname "Service Plan",longtodate(ac.startdate) ...Contract and Service Plans details - Query Report (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...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", ...Request created in out of business hours
This report is used to find the request created out of business hours. Based on this report, resources can be allocated to manage the load. To make any changes to a query, refer to the KB article below. ...