Difference between revisions of "SPBilling"

Revision as of 17:27, 16 June 2016

Remove Maintenance previous balance

1. Open SQL Yog connect to 89 server
2. Run this query
3. Remember changes the date do you want to remove the balanced.
4. SELECT * FROM spbilling.tblmbilling WHERE dtencoded >= '2016-03-10'
5. UPDATE spbilling.tblmbilling SET prevbal = "0.00", prevpenalty = "0.00" , prevpmt = '0.00' WHERE dtencoded >= '2016-03-10'

Remove Water previous balance

1. Open SQL Yog connect to 89 server
2. Run this query
3. Remember changes the date do you want to remove the balanced.
4. SELECT * FROM spbilling.tblwbilling WHERE dtencoded >= '2016-03-10'
5. UPDATE spbilling.tblwbilling SET prevbal = "0.00", prevpenalty = "0.00" , prevpmt = '0.00' WHERE dtencoded >= '2016-03-10'

SPBILLING WATER_READING

SELECT bb.tenant,bb.address,bb.acctno, (SELECT startperiod FROM spbilling.tblwbillperiod WHERE DATE(aa.dtencoded) BETWEEN DATE(startperiod) AND DATE(endperiod) AND siteno = bb.siteno) AS startperiod, (SELECT endperiod FROM spbilling.tblwbillperiod WHERE DATE(aa.dtencoded) BETWEEN DATE(startperiod) AND DATE(endperiod) AND siteno = bb.siteno) AS endperiod, (SELECT billdate FROM spbilling.tblwbillperiod WHERE DATE(aa.dtencoded) BETWEEN DATE(startperiod) AND DATE(endperiod) AND siteno = bb.siteno) AS billdate, (SELECT duedate FROM spbilling.tblwbillperiod WHERE DATE(aa.dtencoded) BETWEEN DATE(startperiod) AND DATE(endperiod) AND siteno = bb.siteno) AS duedate, (SELECT locator FROM spbilling.`tbllocator` WHERE locatorno = bb.locatorno) AS locator, aa.* FROM spbilling.`tblwbilling` aa JOIN spbilling.tbllot bb ON aa.meterno = bb.meterno AND aa.dtencoded >= DATE('2014-01-01') WHERE aa.meterno = bb.meterno AND aa.dtencoded >= DATE('2014-01-01')