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) "Start Date", longtodate(ac.expirydate) "Expiry Date",COUNT(wo.workorderid) "Consumed number of Requests",MAX(sp.fixedmonthlyunits)-MAX(bc.consumedunits) "Requests 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 sd.internalname= 'Closed' and plantype='Charge Per Incident'
group by ad.org_name,ac.startdate,ac.expirydate,sp.serviceplanname
********************************************************************************************************
Consumed units (Charge by Hour) :
PGSQL & MSSQL:
select ad.org_name "Account",sp.serviceplanname "Service Plan",longtodate(ac.startdate) "Start Date", longtodate(ac.expirydate) "Expiry Date", (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 sd.internalname= 'Closed' and plantype='Charge Per Hour'
group by ad.org_name,ac.startdate,ac.expirydate,sp.serviceplanname
********************************************************************************************************
New to ADSelfService Plus?
Related Articles
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 ...Query to find out who created Accounts (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Execute the below queries under Reports->New Query Report. select org_id, org_name from accountdefinition ad where ad.org_name in('Requester Name', 'Palanivel Palras','Muhammad Nadeem Khan') Note down ...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 shared requests (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Shared to Tech: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ...Query for Billable and Non Billable worklogs of a request (PGSQL)
Tested in Build PGSQL (14300) Non Billable Requests pgsql SELECT wo.workorderid "Request ID", wo.TITLE AS "Subject", longtodate(ct.CREATEDTIME) AS "Time Spent Created Time", rctd.FIRST_NAME AS "Time Spent Technician", TO_CHAR((sum(ct.TIMESPENT)/1000 ...