Query to show contract billing details with requests

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') as varchar) "Time Spent", LONGTODATE(wo.createdtime) "Creation Date", LONGTODATE(wo.completedtime) "Closing Date" 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
LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID 
LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID 
where ac.accountid in ($Account) AND wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth>
group by wo.workorderid, wo.createdtime, wo.completedtime, ad.org_name, sp.timeperiod, ac.startdate,ac.expirydate,sp.serviceplanname,sp.FIXEDMONTHLYUNITS,ac.contractno,ac.contractname ORDER BY 2 ASC


          • 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", ...
          • 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 ...
          • 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 solution details with approver and associated requests

            MSSQL: SELECT solution.solutionid "Solution ID" , Ad.org_name "Account", max(Solution.TITLE) "Solution Title", max(au.first_name) "Solution Approver", max(KB_Topics.TOPICNAME) "Solution Topic", max(Solution_Keywords.keyword) "Keywords", ...
          • Query for request attachment details

            Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account",  wo.WORKORDERID AS "Request ID",  wo.TITLE AS "Subject",  ti.FIRST_NAME AS "Technician",  sa.ATTACHMENTNAME "Attachment Name", sa.ATTACHMENTPATH "Attachment Path" FROM ...