Difference between revisions of "SPBilling"
| Line 1: | Line 1: | ||
| − | == Remove Maintenance previous balance == | + | == '''Remove Maintenance previous balance''' == |
# Open SQL Yog connect to 89 server | # Open SQL Yog connect to 89 server | ||
| Line 8: | Line 8: | ||
| − | == Remove Water previous balance == | + | == '''Remove Water previous balance''' == |
# Open SQL Yog connect to 89 server | # Open SQL Yog connect to 89 server | ||
| Line 16: | Line 16: | ||
# UPDATE spbilling.tblwbilling SET prevbal = "0.00", prevpenalty = "0.00" , prevpmt = '0.00' WHERE dtencoded >= '2016-03-10' | # UPDATE spbilling.tblwbilling SET prevbal = "0.00", prevpenalty = "0.00" , prevpmt = '0.00' WHERE dtencoded >= '2016-03-10' | ||
| − | == SPBILLING WATER_READING== | + | == '''SPBILLING WATER_READING''' == |
SELECT bb.tenant,bb.address,bb.acctno, | SELECT bb.tenant,bb.address,bb.acctno, | ||
Revision as of 17:28, 16 June 2016
Remove Maintenance previous balance
- Open SQL Yog connect to 89 server
- Run this query
- Remember changes the date do you want to remove the balanced.
- SELECT * FROM spbilling.tblmbilling WHERE dtencoded >= '2016-03-10'
- UPDATE spbilling.tblmbilling SET prevbal = "0.00", prevpenalty = "0.00" , prevpmt = '0.00' WHERE dtencoded >= '2016-03-10'
Remove Water previous balance
- Open SQL Yog connect to 89 server
- Run this query
- Remember changes the date do you want to remove the balanced.
- SELECT * FROM spbilling.tblwbilling WHERE dtencoded >= '2016-03-10'
- 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')
