Charge rate change need re-calculate whole report.
Step 1
backup old SQL table see here
https://transparentgov.net/cleargov1/1599/how-to-backup-sql-table
Step 2
Before rate change:
SQL script for prorated acress:
--*Prorate Acreage (Rounds PRCACREAGE to nearest whole number and populates in PRORATEACRES field)*--
which means, 5.01 = 5,
6.51 = 7 anything over 0.5 will round to next number, anyting lower than 0.5 will round to previous number.
UPDATE COV_STANDBY_AVAILABILITY SET PRORATEACRES = ROUND(PRCACREAGE,0) WHERE PRCACREAGE > 1 AND STANDBYYEAR = 2024
UPDATE COV_STANDBY_AVAILABILITY SET PRORATEACRES = 1 where PRCACREAGE <= 1 AND STANDBYYEAR = 2024
Step 3
New rate change
Rounds PRCACREAGE to NEXT whole number and populates in PRORATEACRES field
which means 6.01 = 7
UPDATE COV_STANDBY_AVAILABILITY SET PRORATEACRES = CEILING(PRCACREAGE) WHERE PRCACREAGE > 1 AND STANDBYYEAR = 2024
UPDATE COV_STANDBY_AVAILABILITY SET PRORATEACRES = 1 where PRCACREAGE <= 1 AND STANDBYYEAR = 2024
Confirm the new rate take effect
Step 5
use new charge rate, Calculate water district #2 FEEAMOUNT
UPDATE COV_STANDBY_AVAILABILITY SET FEEAMOUNT = 15 WHERE WDISTRICT = 'ID-2' and PRCACREAGE <= 1 AND STANDBYYEAR = 2024
UPDATE COV_STANDBY_AVAILABILITY SET FEEAMOUNT = PRORATEACRES * 15 WHERE WDISTRICT = 'ID-2' and PRCACREAGE > 1 AND STANDBYYEAR = 2024
Step 6
use new charge rate, Calculate water district #1 FEEAMOUNT
UPDATE COV_STANDBY_AVAILABILITY SET FEEAMOUNT = PRORATEACRES * 10
from COV_STANDBY_AVAILABILITY as SB
left outer join VPISPROD.dbo.STANDBY_ACTIVE_ID1_ACCOUNTS as A on SB.APN = A.APN
where A.APN is null
and WDISTRICT = 'ID-1'
AND STANDBYYEAR = 2024
Step 7
Run this test script to confirm rate change is successful.
So far rate change completed in SQL table, re-run all SQL to generate all report export to excel.