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

          • 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 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 to show parent and child requests (Linked Request) and its details. PGSQL & MSSQL

            Please go to Reports-New Query Report and execute this query. select woparent.WORKORDERID "Parent request ID", aaauparentreq.first_name "Parent Requester", woparent.title "Parent Subject", stdparent.statusname "Parent Request Status", ...
          • Query report to show Open requests without open tasks

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...
          • Query to show Contract details with assets associated

            PGSQL: SELECT mcdt.customcontractid "Contract ID", mcdt.CONTRACTNAME AS "Contract Name", mcdt.customcontractid "Contract ID", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) AS "Created Time", ad.ORG_NAME AS "Account",  ...