Difference between revisions of "SPBilling"
| (4 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | == Remove Maintenance previous balance == | + | [[Main_Page | Main Page]] > [[Application_Development_Team | Application Development Team]] > [[System_Support | System Support]] |
| + | |||
| + | == '''Remove Maintenance previous balance''' == | ||
# Open SQL Yog connect to 89 server | # Open SQL Yog connect to 89 server | ||
| Line 7: | Line 9: | ||
# UPDATE spbilling.tblmbilling SET prevbal = "0.00", prevpenalty = "0.00" , prevpmt = '0.00' 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''' == | |
| − | == Remove Water previous balance == | + | |
# Open SQL Yog connect to 89 server | # Open SQL Yog connect to 89 server | ||
| Line 15: | Line 16: | ||
# SELECT * FROM spbilling.tblwbilling WHERE dtencoded >= '2016-03-10' | # 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' | # 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') | ||
| + | |||
| + | [[Main_Page | Main Page]] > [[Application_Development_Team | Application Development Team]] > [[System_Support | System Support]] | ||
Latest revision as of 09:15, 1 September 2016
Main Page > Application Development Team > System Support
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')
