While working on this bug I came across several situations that would produce incorrect budgets. All of them relate to deleted campaigns but each of the scenarios is unique. Here are the cases that need to be tested. I have explained them in further detail below. A. regular campaign deletion B. multiple campaign addition and deletions in the same day C. deleting 2 campaigns in one request Here's what happens when you delete a campaign: The endDates for all of the account's budget entries in st-tracker.adMaxNominalMonthlyBudgets are set to today (the day the campaign got deleted). Budgets for today (the day of deletion) remain unchanged. Then, new entries are made for all the active campaigns with a start date of tomorrow (the day after the campaign was deleted) and an end date of null. The budget for the deleted campaign is divided up and reallocated to the active campaign entries for tomorrow. If another deletion happens on this day, the deleted campaign's budget entry for tomorrow is removed and the budget associated with it is reallocated to the active campaign entries for tomorrow. If a new campaign is added on this day, budget entries for both today and tomorrow are created and the budgets are recalculated to reflect the additional campaign. Always add up the budgets with the same end date to make sure they equal the total budget for the account (minus the markup). ** Remember that the budget totals you see in the DB won't exactly reflect the total order budget in the UI because of the markup ** ===== A. Regular campaign deletion example ===== 1. Create a new order with 2 BA + BL combos (Beds & Breakfasts, VT and Cabin Rentals in CO) and a budget of $8.00 mysql> SELECT * FROM `st-tracker`.admaxNominalMonthlyBudgets anmb join `st-tracker`.admaxCampaigns ac on ac.id=anmb.admaxCampaignID where description like '88f842fa-98e0-4745-9f51-3afe663be461%' order by startDate asc; +------+-----------------+------------+---------+---------+------+------------+----------------------------------------------------+-----------+--------------------+--------+ | id | admaxCampaignID | startDate | endDate | budget | id | identifier | description | accountID | campaignGroupingID | status | +------+-----------------+------------+---------+---------+------+------------+----------------------------------------------------+-----------+--------------------+--------+ | 1687 | 1130 | 2013-11-25 | NULL | 3.07692 | 1130 | 3645 | 88f842fa-98e0-4745-9f51-3afe663be461_11979_44_3645 | 591 | 0 | Active | | 1686 | 1129 | 2013-11-25 | NULL | 3.07692 | 1129 | 3644 | 88f842fa-98e0-4745-9f51-3afe663be461_10004_29_3644 | 591 | 0 | Active | +------+-----------------+------------+---------+---------+------+------------+----------------------------------------------------+-----------+--------------------+--------+ 2 rows in set (0.06 sec) 2. Delete one of the BA + BL combos (Beds & Breakfasts, VT) mysql> SELECT * FROM `st-tracker`.admaxNominalMonthlyBudgets anmb join `st-tracker`.admaxCampaigns ac on ac.id=anmb.admaxCampaignID where description like '88f842fa-98e0-4745-9f51-3afe663be461%' order by startDate asc; +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | id | admaxCampaignID | startDate | endDate | budget | id | identifier | description | accountID | campaignGroupingID | status | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | 1687 | 1130 | 2013-11-25 | 2013-11-25 | 3.07692 | 1130 | 3645 | 88f842fa-98e0-4745-9f51-3afe663be461_11979_44_3645 | 591 | 0 | Active | | 1686 | 1129 | 2013-11-25 | 2013-11-25 | 3.07692 | 1129 | 3644 | 88f842fa-98e0-4745-9f51-3afe663be461_10004_29_3644 | 591 | 0 | Deleted | | 1689 | 1130 | 2013-11-26 | NULL | 6.15384 | 1130 | 3645 | 88f842fa-98e0-4745-9f51-3afe663be461_11979_44_3645 | 591 | 0 | Active | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ 3 rows in set (0.06 sec) Observe that the budget entries for all the campaigns (that at some point in the day were active) have an end date of today and the active campaign has a budget entry for tomorrow with the original budget as well as the reallocated budget of the deleted campaign. ===== B. Multiple campaign addition and deletions in the same day ===== 1. Create a new order with 1 BA + BL combo (Beds & Breakfasts, VT) and a budget of $4.00 mysql> SELECT * FROM `st-tracker`.admaxNominalMonthlyBudgets anmb join `st-tracker`.admaxCampaigns ac on ac.id=anmb.admaxCampaignID where description like '981e1bab-17fe-4c39-85f9-018a9d4151b1%'; +------+-----------------+------------+---------+---------+------+------------+----------------------------------------------------+-----------+--------------------+--------+ | id | admaxCampaignID | startDate | endDate | budget | id | identifier | description | accountID | campaignGroupingID | status | +------+-----------------+------------+---------+---------+------+------------+----------------------------------------------------+-----------+--------------------+--------+ | 1580 | 1104 | 2013-11-25 | NULL | 3.07692 | 1104 | 3569 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3569 | 585 | 0 | Active | +------+-----------------+------------+---------+---------+------+------------+----------------------------------------------------+-----------+--------------------+--------+ 1 row in set (0.06 sec) 2. Swap the current BA + BL combo out for a new one (Cabin Rentals, CA), this step is one campaign add and one campaign delete mysql> SELECT * FROM `st-tracker`.admaxNominalMonthlyBudgets anmb join `st-tracker`.admaxCampaigns ac on ac.id=anmb.admaxCampaignID where description like '981e1bab-17fe-4c39-85f9-018a9d4151b1%'; +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | id | admaxCampaignID | startDate | endDate | budget | id | identifier | description | accountID | campaignGroupingID | status | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | 1580 | 1104 | 2013-11-25 | 2013-11-25 | 1.53846 | 1104 | 3569 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3569 | 585 | 0 | Deleted | | 1582 | 1105 | 2013-11-25 | 2013-11-25 | 1.53846 | 1105 | 3571 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_11979_57_3571 | 585 | 0 | Active | | 1584 | 1105 | 2013-11-26 | NULL | 3.07692 | 1105 | 3571 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_11979_57_3571 | 585 | 0 | Active | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ 3 rows in set (0.06 sec) Observe that both the deleted and new campaigns have budget entries for today and each has half of the total budget allocated to it. The active campaign has an entry for tomorrow with all of the budget allocated to it. 3. Next, make another campaign deletion by swapping the current BA + BL combo out again for a new one (Beds & Breakfasts, VT). Even though this is the same BA + BL combo that we originally started with it is treated as a new campaign. mysql> SELECT * FROM `st-tracker`.admaxNominalMonthlyBudgets anmb join `st-tracker`.admaxCampaigns ac on ac.id=anmb.admaxCampaignID where description like '981e1bab-17fe-4c39-85f9-018a9d4151b1%' order by startDate asc; +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | id | admaxCampaignID | startDate | endDate | budget | id | identifier | description | accountID | campaignGroupingID | status | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | 1580 | 1104 | 2013-11-25 | 2013-11-25 | 1.02564 | 1104 | 3569 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3569 | 585 | 0 | Deleted | | 1582 | 1105 | 2013-11-25 | 2013-11-25 | 1.02564 | 1105 | 3571 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_11979_57_3571 | 585 | 0 | Deleted | | 1586 | 1106 | 2013-11-25 | 2013-11-25 | 1.02564 | 1106 | 3574 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3574 | 585 | 0 | Active | | 1589 | 1106 | 2013-11-26 | NULL | 3.07692 | 1106 | 3574 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3574 | 585 | 0 | Active | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ 4 rows in set (0.06 sec) Observe that there are 3 budget entries for today, each reflecting a campaign that was active at some point today. The budget is evenly divided among today's campaigns. There is only ONE budget entry for tomorrow and that is for the campaign that is currently active (tomorrow's budget entry for campaign 1105 was deleted since the campaign was deleted today). Since there is only one active campaign for tomorrow it has all of the budget allocated to it. 4. Add 2 more BA + BL combos (Hotels, CO and Campgrounds, AZ) and increase the budget to $12. mysql> SELECT * FROM `st-tracker`.admaxNominalMonthlyBudgets anmb join `st-tracker`.admaxCampaigns ac on ac.id=anmb.admaxCampaignID where description like '981e1bab-17fe-4c39-85f9-018a9d4151b1%' order by startDate asc; +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | id | admaxCampaignID | startDate | endDate | budget | id | identifier | description | accountID | campaignGroupingID | status | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | 1597 | 1108 | 2013-11-25 | 2013-11-25 | 1.84615 | 1108 | 3578 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10006_55_3578 | 585 | 0 | Active | | 1592 | 1107 | 2013-11-25 | 2013-11-25 | 1.84615 | 1107 | 3577 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10002_44_3577 | 585 | 0 | Active | | 1586 | 1106 | 2013-11-25 | 2013-11-25 | 1.84615 | 1106 | 3574 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3574 | 585 | 0 | Active | | 1582 | 1105 | 2013-11-25 | 2013-11-25 | 1.84615 | 1105 | 3571 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_11979_57_3571 | 585 | 0 | Deleted | | 1580 | 1104 | 2013-11-25 | 2013-11-25 | 1.84615 | 1104 | 3569 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3569 | 585 | 0 | Deleted | | 1602 | 1108 | 2013-11-26 | NULL | 3.07692 | 1108 | 3578 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10006_55_3578 | 585 | 0 | Active | | 1596 | 1107 | 2013-11-26 | NULL | 3.07692 | 1107 | 3577 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10002_44_3577 | 585 | 0 | Active | | 1589 | 1106 | 2013-11-26 | NULL | 3.07692 | 1106 | 3574 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3574 | 585 | 0 | Active | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ 8 rows in set (0.06 sec) Observe that there are a total of 5 budget entries for today (the 3 active campaigns plus the 2 that were deleted today). The 3 active campaigns all have budget entries for tomorrow and the budget total for today is the same as the budget total for tomorrow: 1.84615 * 5 = 9.23075 = 3.07692 * 3. Also notice that the budget itself has been updated. 5. Remove 2 of the BA + BL combos (Beds & Breakfasts, VT and Hotels, CO) mysql> SELECT * FROM `st-tracker`.admaxNominalMonthlyBudgets anmb join `st-tracker`.admaxCampaigns ac on ac.id=anmb.admaxCampaignID where description like '981e1bab-17fe-4c39-85f9-018a9d4151b1%' order by startDate asc; +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | id | admaxCampaignID | startDate | endDate | budget | id | identifier | des cription | accountID | campaignGroupingID | status | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | 1586 | 1106 | 2013-11-25 | 2013-11-25 | 1.84615 | 1106 | 3574 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3574 | 585 | 0 | Deleted | | 1582 | 1105 | 2013-11-25 | 2013-11-25 | 1.84615 | 1105 | 3571 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_11979_57_3571 | 585 | 0 | Deleted | | 1592 | 1107 | 2013-11-25 | 2013-11-25 | 1.84615 | 1107 | 3577 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10002_44_3577 | 585 | 0 | Deleted | | 1597 | 1108 | 2013-11-25 | 2013-11-25 | 1.84615 | 1108 | 3578 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10006_55_3578 | 585 | 0 | Active | | 1580 | 1104 | 2013-11-25 | 2013-11-25 | 1.84615 | 1104 | 3569 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3569 | 585 | 0 | Deleted | | 1602 | 1108 | 2013-11-26 | NULL | 9.23076 | 1108 | 3578 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10006_55_3578 | 585 | 0 | Active | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ 6 rows in set (0.06 sec) Now observe that since there is only one active campaign there is only one budget entry for tomorrow and it has all the budget. Since no new campaigns were added and the total account budget was not changed, the budget entries for today remain untoched. 6. Change the budget back to $4 mysql> SELECT * FROM `st-tracker`.admaxNominalMonthlyBudgets anmb join `st-tracker`.admaxCampaigns ac on ac.id=anmb.admaxCampaignID where description like '981e1bab-17fe-4c39-85f9-018a9d4151b1%' order by startDate asc; +------+-----------------+------------+------------+----------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | id | admaxCampaignID | startDate | endDate | budget | id | identifier | description | accountID | campaignGroupingID | status | +------+-----------------+------------+------------+----------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | 1597 | 1108 | 2013-11-25 | 2013-11-25 | 0.615385 | 1108 | 3578 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10006_55_3578 | 585 | 0 | Active | | 1580 | 1104 | 2013-11-25 | 2013-11-25 | 0.615385 | 1104 | 3569 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3569 | 585 | 0 | Deleted | | 1586 | 1106 | 2013-11-25 | 2013-11-25 | 0.615385 | 1106 | 3574 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10004_29_3574 | 585 | 0 | Deleted | | 1582 | 1105 | 2013-11-25 | 2013-11-25 | 0.615385 | 1105 | 3571 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_11979_57_3571 | 585 | 0 | Deleted | | 1592 | 1107 | 2013-11-25 | 2013-11-25 | 0.615385 | 1107 | 3577 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10002_44_3577 | 585 | 0 | Deleted | | 1602 | 1108 | 2013-11-26 | NULL | 3.07692 | 1108 | 3578 | 981e1bab-17fe-4c39-85f9-018a9d4151b1_10006_55_3578 | 585 | 0 | Active | +------+-----------------+------------+------------+----------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ 6 rows in set (0.07 sec) Since the total account budget was changed, all the budget entries for today and tomorrow have been updated to reflect this change. Again, the total budget for today is the same as the total budget for tomorrow: 0.615385 * 5 = 3.076925 (which is the same as steps B1, B2, and B3). ===== Deleting two campaigns in one request ==== 1. Create an order with 4 BA + BL combos mysql> SELECT * FROM `st-tracker`.admaxNominalMonthlyBudgets anmb join `st-tracker`.admaxCampaigns ac on ac.id=anmb.admaxCampaignID where description like '90e36686-9985-44e2-b33f-8b3766d3c01f%' order by startDate asc; +------+-----------------+------------+---------+---------+------+------------+----------------------------------------------------+-----------+--------------------+--------+ | id | admaxCampaignID | startDate | endDate | budget | id | identifier | description | accountID | campaignGroupingID | status | +------+-----------------+------------+---------+---------+------+------------+----------------------------------------------------+-----------+--------------------+--------+ | 1692 | 1132 | 2013-11-25 | NULL | 3.07692 | 1132 | 3651 | 90e36686-9985-44e2-b33f-8b3766d3c01f_10006_55_3651 | 592 | 0 | Active | | 1693 | 1133 | 2013-11-25 | NULL | 3.07692 | 1133 | 3652 | 90e36686-9985-44e2-b33f-8b3766d3c01f_11262_57_3652 | 592 | 0 | Active | | 1691 | 1131 | 2013-11-25 | NULL | 3.07692 | 1131 | 3650 | 90e36686-9985-44e2-b33f-8b3766d3c01f_10004_29_3650 | 592 | 0 | Active | | 1694 | 1134 | 2013-11-25 | NULL | 3.07692 | 1134 | 3653 | 90e36686-9985-44e2-b33f-8b3766d3c01f_11979_44_3653 | 592 | 0 | Active | +------+-----------------+------------+---------+---------+------+------------+----------------------------------------------------+-----------+--------------------+--------+ 4 rows in set (0.06 sec) There are 4 entries for each of the active campaigns with the budget evenly divided among them. 2. Delete 2 of the BA + BL combos mysql> SELECT * FROM `st-tracker`.admaxNominalMonthlyBudgets anmb join `st-tracker`.admaxCampaigns ac on ac.id=anmb.admaxCampaignID where description like '90e36686-9985-44e2-b33f-8b3766d3c01f%' order by startDate asc; +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | id | admaxCampaignID | startDate | endDate | budget | id | identifier | description | accountID | campaignGroupingID | status | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ | 1692 | 1132 | 2013-11-25 | 2013-11-25 | 3.07692 | 1132 | 3651 | 90e36686-9985-44e2-b33f-8b3766d3c01f_10006_55_3651 | 592 | 0 | Deleted | | 1694 | 1134 | 2013-11-25 | 2013-11-25 | 3.07692 | 1134 | 3653 | 90e36686-9985-44e2-b33f-8b3766d3c01f_11979_44_3653 | 592 | 0 | Active | | 1693 | 1133 | 2013-11-25 | 2013-11-25 | 3.07692 | 1133 | 3652 | 90e36686-9985-44e2-b33f-8b3766d3c01f_11262_57_3652 | 592 | 0 | Active | | 1691 | 1131 | 2013-11-25 | 2013-11-25 | 3.07692 | 1131 | 3650 | 90e36686-9985-44e2-b33f-8b3766d3c01f_10004_29_3650 | 592 | 0 | Deleted | | 1698 | 1134 | 2013-11-26 | NULL | 6.15384 | 1134 | 3653 | 90e36686-9985-44e2-b33f-8b3766d3c01f_11979_44_3653 | 592 | 0 | Active | | 1696 | 1133 | 2013-11-26 | NULL | 6.15384 | 1133 | 3652 | 90e36686-9985-44e2-b33f-8b3766d3c01f_11262_57_3652 | 592 | 0 | Active | +------+-----------------+------------+------------+---------+------+------------+----------------------------------------------------+-----------+--------------------+---------+ 6 rows in set (0.06 sec) The 4 entries that were created in step 1 have end dates of today and the two remaining active campaigns have entries for tomorrow with the total budget divide between the two of them.