Allowance, used and remaining Hours

Allowance, used and remaining Hours

PGSQL & MSSQL:

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

          • Related Articles

          • Consumed Units

            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 ...
          • Query to show contract billing details with requests

            PGSQL: ​ select ad.org_name "Account", wo.workorderid "Request ID", wo.workorderid "Request ID", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name", sp.timeperiod "Bill Cycle", cast((sum(ct.TIMESPENT)/1000 * interval '1 second') ...
          • 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 total time spent for a ticket

            PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS "Subject", rtdef.NAME AS "Request Type", ti.FIRST_NAME AS "Technician", sdo.NAME AS "Site", ...
          • Ports used during asset scan.

            Kindly refer the link below to know more about ports used during scan. https://www.manageengine.com/products/asset-explorer/help/scanning_it_assets/ports-during-scan.html