Query Report to find the Contract details in SCP 11.0

Query Report to find the Contract details in SCP 11.0

SELECT ad.ORG_NAME AS "Account", ac.CONTRACTNAME AS "Contract Name", ac.CONTRACTNO AS "Contract Number", ( case when sp.PLANTYPE like 'Charge Per Hour' then concat(cast(((bc.consumedunits/(1000))/(60*60)) as varchar), ':' ,cast((((bc.consumedunits/(1000))/60)%60) as varchar))  else  cast(bc.consumedunits as varchar) end) "Allowance Consumed",sp.fixedmonthlyunits "fixedmonthlyunits", bc.TOPUPALLOWANCE , (case when sp.PLANTYPE like 'Charge Per Hour' then concat(cast((((COALESCE ((SELECT CLOSINGBALANCE FROM billhistory WHERE billhistory.contractid=ac.contractid GROUP BY billid ORDER BY billid DESC LIMIT 1 ),0 ) + (case when sp.PLANTYPE like 'Charge Per Hour' then sp.fixedmonthlyunits*3600000 else sp.fixedmonthlyunits end) -(bc.consumedunits)+(case when sp.PLANTYPE like 'Charge Per Hour' then bc.TOPUPALLOWANCE*3600000 else bc.TOPUPALLOWANCE end))/(1000))/(60*60)) as varchar), ':' ,cast(((((COALESCE ((SELECT CLOSINGBALANCE FROM billhistory WHERE billhistory.contractid=ac.contractid GROUP BY billid ORDER BY billid DESC LIMIT 1 ),0 ) + (case when sp.PLANTYPE like 'Charge Per Hour' then sp.fixedmonthlyunits*3600000 else sp.fixedmonthlyunits end) -(bc.consumedunits)+(case when sp.PLANTYPE like 'Charge Per Hour' then bc.TOPUPALLOWANCE*3600000 else bc.TOPUPALLOWANCE end))/(1000))/60)%60) as varchar)) else cast((COALESCE ((SELECT CLOSINGBALANCE FROM billhistory WHERE billhistory.contractid=ac.contractid GROUP BY billid ORDER BY billid DESC LIMIT 1 ),0 ) + (case when sp.PLANTYPE like 'Charge Per Hour' then sp.fixedmonthlyunits*3600000 else sp.fixedmonthlyunits end) -(bc.consumedunits)+(case when sp.PLANTYPE like 'Charge Per Hour' then bc.TOPUPALLOWANCE*3600000 else bc.TOPUPALLOWANCE end)) as varchar) end) "Allowance Remaining", sp.PLANTYPE  FROM AccountContract ac LEFT JOIN AccountDefinition ad ON ac.ACCOUNTID=ad.ORG_ID left join billcycle bc ON ac.contractid=bc.contractid left join serviceplan sp ON ac.serviceplanid=sp.serviceplanid where sp.PLANTYPE NOT LIKE 'Fixed Charge';

Output :