Version : 11026

DB : MSSQL

OUTPUT :

SELECT ac.CONTRACTNAME AS "Contract Name",
  ad.ORG_NAME AS "Account",
  accmgr_user.FIRST_NAME "Account Manager",
  sp.serviceplanname as "Support Plan name",
  ac.contractno as "Contract Number",
  CASE WHEN ac.isactivecontract = 'true' THEN 'Active' WHEN (ac.isactivecontract = 'false' AND
  ac.STARTDATE > CAST(GETDATE() as bigint))
  THEN 'Upcoming' ELSE 'Expired' END as "Status of Contract",
  longtodate(ac.STARTDATE) AS "Start Date",
  longtodate(ac.EXPIRYDATE) AS "Expiry Date",
  sp.fixedmonthlyunits as "Total Allowances",
  bc.consumedunits as "Total consumed Allowances",
  CASE WHEN (sp.fixedmonthlyunits - bc.consumedunits) > 0 THEN (sp.fixedmonthlyunits - bc.consumedunits) ELSE 0 END as "Total remaining Allowances" ,
  ( bc.consumedunits / Max(sp.fixedmonthlyunits) OVER() ) * 100 AS "Percentage consumed",
  tp.topupplanname as "Top-up plan", sp.plantype as "Contract Type" FROM AccountContract ac
  LEFT JOIN AccountDefinition ad ON ac.ACCOUNTID=ad.ORG_ID
  LEFT JOIN Serviceplan sp ON ac.serviceplanid=sp.serviceplanid
  left join serviceplantopupplanmapping sptp on sp.serviceplanid=sptp.serviceplanid
  left join topupplan tp on sptp.topupplanid=tp.topupplanid
  left join contractaccountmapping cam on cam.accountid=ad.org_id
  left join maintenancecontract mc on mc.contractid=cam.contractid
  left join contractstatus cs on mc.statusid=cs.statusid
  LEFT JOIN PortalAccounts poa ON ad.ORG_ID=poa.ACCOUNTID
  LEFT JOIN AaaUser accmgr_user on poa.SALESREP_ID = accmgr_user.USER_ID
  left join billcycle bc on bc.contractid=ac.contractid where sp.plantype = 'Charge Per Incident'