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
          • Related Articles

          • Query to show service catalog template details

            select rtl.templatename "Service Template Name", rtl.comments "Template description", rtf.description "Description field", cd. categoryname "Category", scd.name "Subcategory", id.name "Item" from requesttemplate_list rtl LEFT JOIN ...
          • Query to show purchase order full details

            PGSQL: SELECT po.POCUSTOMID AS "Order No.",poi.serialno "S.No", poaddfield.UDF_CHAR1 AS "Legal Entity", po.PONAME AS "PO Name", pos.STATUSNAME AS "PO Status", cy.currencyname "Currency",  paao.NAME AS "Vendor Name", po.SHIPTO AS "Ship To", po.BILLTO ...
          • Query to view task details with account

            For Postgres and MS SQL SELECT org_name "Account", "taskmilestone"."PROJECTID" AS "Project id", "taskdet"."TASKID" AS "Task ID", "taskdet"."TITLE" AS "Title", "taskcreatedby"."FIRST_NAME" AS "Created By", "taskowner"."FIRST_NAME" AS "Owner", ...
          • Query to show requesters who are service and PO approvers

            PGSQL & MSSQL: select ad.org_name "Account Name", sdo.name "Site Name", au.first_name "First Name", aci.emailid "Email ID",aci.landline "Telephone", aci.mobile "Mobile",po.approverstatus "Purchase Request Approver", sr.approverstatus "Service Request ...
          • Query to list Service Request Approver and Purchase Request Approver

            Postgres & SQL Select aau.first_name "Name", po.approverstatus "Purchase Request Approver", sr.approverstatus "Service Request Approver", adef.org_name "Account" from aaauser aau left join POApproverDetails po on po.approver=aau.user_id left join ...