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", ...Query to fetch the fields in the templates (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) The query fetches all the fields with display name used across Request templates PQSQL: select Distinct (CASE when fc.field_name like 'udf_%' then ca.aliasname else fc.field_name END) "Fields", ...