Query to retrieve the active and expired CPH contracts details
Tested in: 14620, 14610 and 14306
Query 1:
To return the Active CPH contracts details:
select ad.org_name "Account", ad.org_name "Account",
sp.serviceplanname "Service Plan",
ac.CONTRACTNO "Contract No",
longtodate(ac.startdate) "Contract Start Date",
longtodate(ac.expirydate) "Contract Expiry Date",
sp.fixedmonthlyunits "Total Allowance (Hours)",
MAX(tph.topupunits) "Bring Forward Hours",
(MAX(bc.consumedunits)/3600000) "Hours Used",
case when tph.topupunits > 0 then MAX(sp.fixedmonthlyunits)-(MAX(bc.consumedunits)/3600000) + MAX(tph.topupunits) else MAX(sp.fixedmonthlyunits)-(MAX(bc.consumedunits)/3600000) END AS "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
left join topuphistory tph on ac.contractid=tph.contractid
where ac.isactivecontract ='true' and sp.plantype='Charge Per Hour' and ac.accountid in ($Account)
group by ad.org_name,ac.startdate,ac.expirydate,sp.serviceplanname,sp.FIXEDMONTHLYUNITS,tph.topupunits,ac.contractno order by 1
Query 2:
To return both the Active and Expired CPH contracts details:
select ad.org_name "Account", ad.org_name "Account",
sp.serviceplanname "Service Plan",
ac.CONTRACTNO "Contract No",
longtodate(ac.startdate) "Contract Start Date",
longtodate(ac.expirydate) "Contract Expiry Date",
sp.fixedmonthlyunits "Total Allowance (Hours)",
MAX(tph.topupunits) "Bring Forward Hours",
(MAX(bc.consumedunits)/3600000) "Hours Used",
case when tph.topupunits > 0 then MAX(sp.fixedmonthlyunits)-(MAX(bc.consumedunits)/3600000) + MAX(tph.topupunits) else MAX(sp.fixedmonthlyunits)-(MAX(bc.consumedunits)/3600000) END AS "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
left join topuphistory tph on ac.contractid=tph.contractid
where sp.plantype='Charge Per Hour' and ac.accountid in ($Account)
group by ad.org_name,ac.startdate,ac.expirydate,sp.serviceplanname,sp.FIXEDMONTHLYUNITS,tph.topupunits,ac.contractno order by 1