Contract and Service Plans details - Query Report

Contract and Service Plans details - Query Report

PGSQL & MSSQL:

select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill Cycle",fixedmonthlycharges "Fixed Base Charge",sp.fixedmonthlyunits "Allowance", sp.fixedhourcost "Fixed Cost Per Hour",sp.operationalhourcost "OP Hrs Cost",
sp.nonoperationalhourcost "Non OP Hrs Cost", sp.weekendhourcost "Weekend Cost",sp.holidayhourcost "Holiday Cost", longtodate(ac.startdate) "Contract Start Date", longtodate(ac.expirydate) "Contract Expiry Date", ac.isactivecontract "Is Active Contract", ad.org_name "Account" 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.accountid in ($Account)
group by ad.org_name,ac.startdate,ac.expirydate,sp.serviceplanname,sp.FIXEDMONTHLYUNITS,ac.contractno,ac.contractname,sp.plantype,ac.isactivecontract,sp.timeperiod,sp.fixedmonthlycharges,sp.fixedhourcost,sp.operationalhourcost,sp.nonoperationalhourcost,sp.weekendhourcost,sp.holidayhourcost ORDER BY 8 ASC

With billing mode as prepaid and ONLY active contracts:

select ad.org_name "Account", ac.contractname "Contract Name", ac.isactivecontract "Is Active Contract", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type",sp.fixedmonthlyunits "Total Allowance", sum(ct.timespent/3600000) "Hours Used" , (sp.fixedmonthlyunits)+COALESCE(bh.closingbalance/1000/3600,0)-(sum(ct.timespent)/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 billhistory bh on ac.contractid=bh.contractid
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
left join workordertocharge wotc on wotc.workorderid=wo.workorderid
inner join chargestable ct on ct.chargeid=wotc.chargeid 
left join NOBILLREQUESTCHARGES nbrc on nbrc.requestchargeid=ct.chargeid
where ac.accountid in ($Account) AND ac.isactivecontract='true' and sp.billingmode=1
group by ad.org_name,sp.serviceplanname,sp.FIXEDMONTHLYUNITS,ac.contractname,sp.plantype,ac.isactivecontract, bh.closingbalance ORDER BY 1 ASC
NOTE: ($Account) is incldued in the query. For account specific data, please select the Account at the top from the down

      New to ADSelfService Plus?

        Resources

            • Related Articles

            • Query to show requests with contract billing, service plan and time spent details

              MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "aau"."FIRST_NAME" AS "Requester", "wo"."TITLE" AS "Subject", "qd"."QUEUENAME" AS "Group", "ti"."FIRST_NAME" AS "Technician", "std"."STATUSNAME" AS "Request Status", ...
            • Query to get Asset contract and its details

              SELECT mcdt.Contractid "URL Contract ID",        mcdt.customcontractid "Custom contractid in details page",        mcdt.CONTRACTNAME "Contract Name",       aao.NAME "Maintenance Vendor Name",        LONGTODATE(mcdt.FROMDATE) "From Date",       ...
            • 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') ...
            • Query to list the custom and query reports and the technician created

              Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name",               au.first_name"Created By" from customreportquery custrep LEFT JOIN customreport_details custrepdet ...
            • Query to show service catalog template details

              select sd.name "Service Name", rtl.templatename "Service Template Name", rtl.comments "Template description", rtf.description "Description field" from requesttemplate_list rtl LEFT JOIN requesttemplate_fields rtf ON rtl.templateid=rtf.templateid LEFT ...