mysql> select * from admaxBudgetCapMultipliers; +----+--------------+-----------+------------+---------+---------------------+ | id | elementType | elementID | startDate | endDate | budgetCapMultiplier | +----+--------------+-----------+------------+---------+---------------------+ | 1 | distribution | 3 | 2014-03-01 | NULL | 3 | | 2 | distribution | 147 | 2014-03-01 | NULL | 1 | +----+--------------+-----------+------------+---------+---------------------+ 2 rows in set (0.00 sec) mysql> select * from `st-tracker`.admaxSearchEngineAccountActualDailyBudgets where searchEngineAccountid in (SELECT id from tsacommon.searchEngineAccounts where accountid = 81719) order by date; +-------+-----------------------+------------+----------+ | id | searchEngineAccountID | date | budget | +-------+-----------------------+------------+----------+ | 64723 | 595596 | 2014-06-24 | 0.516667 | | 64724 | 595597 | 2014-06-24 | 0.516667 | | 64725 | 595598 | 2014-06-24 | 0.516667 | | 64726 | 595599 | 2014-06-24 | 0.516667 | | 64730 | 595599 | 2014-06-25 | 0.783333 | | 64729 | 595598 | 2014-06-25 | 0.783333 | | 64728 | 595597 | 2014-06-25 | 0.783333 | | 64727 | 595596 | 2014-06-25 | 0.783333 | | 64734 | 595599 | 2014-06-26 | 1.05 | | 64733 | 595598 | 2014-06-26 | 1.05 | | 64732 | 595597 | 2014-06-26 | 1.05 | | 64731 | 595596 | 2014-06-26 | 1.05 | | 64738 | 595599 | 2014-06-27 | 1.52775 | | 64737 | 595598 | 2014-06-27 | 1.52775 | | 64736 | 595597 | 2014-06-27 | 1.52775 | | 64735 | 595596 | 2014-06-27 | 1.52775 | | 64739 | 595596 | 2014-06-28 | 1.52775 | | 64740 | 595597 | 2014-06-28 | 1.52775 | | 64741 | 595598 | 2014-06-28 | 1.52775 | | 64742 | 595599 | 2014-06-28 | 1.52775 | +-------+-----------------------+------------+----------+ 20 rows in set (0.01 sec) mysql> select * from `st-tracker`.admaxActualDailyBudgets where admaxCampaignID in(5070,5071); +-------+-----------------+---------+------------+---------+ | id | admaxCampaignID | type | date | budget | +-------+-----------------+---------+------------+---------+ | 32562 | 5070 | boosted | 2014-06-24 | 1.03333 | | 32564 | 5070 | boosted | 2014-06-25 | 1.56667 | | 32566 | 5070 | boosted | 2014-06-26 | 2.1 | | 32568 | 5070 | boosted | 2014-06-27 | 3.13333 | | 32570 | 5070 | boosted | 2014-06-28 | 4.16667 | | 32563 | 5071 | boosted | 2014-06-24 | 1.03333 | | 32565 | 5071 | boosted | 2014-06-25 | 1.56667 | | 32567 | 5071 | boosted | 2014-06-26 | 2.1 | | 32569 | 5071 | boosted | 2014-06-27 | 3.13333 | | 32571 | 5071 | boosted | 2014-06-28 | 4.16667 | +-------+-----------------+---------+------------+---------+ mysql> select * from `st-tracker`.admaxCarryovers where admaxCampaignID in(5070,5071); +-------+-----------------+---------+------------+--------------------+-----------------+-----------------+------------------------+-----------------------+ | id | admaxCampaignID | type | date | dedicatedCarryover | commonCarryover | pooledCarryover | globalPoolContribution | globalPooledCarryover | +-------+-----------------+---------+------------+--------------------+-----------------+-----------------+------------------------+-----------------------+ | 32562 | 5070 | boosted | 2014-06-24 | 0 | 0 | 0 | 0 | 0 | | 32564 | 5070 | boosted | 2014-06-25 | 0 | 0.533333 | 0.533333 | 0 | 0 | | 32566 | 5070 | boosted | 2014-06-26 | 0 | 1.06667 | 1.06667 | 0 | 0 | | 32568 | 5070 | boosted | 2014-06-27 | 0 | 2.1 | 2.1 | 0 | 0 | | 32570 | 5070 | boosted | 2014-06-28 | 0 | 3.13333 | 3.13333 | 0 | 0 | | 32563 | 5071 | boosted | 2014-06-24 | 0 | 0 | 0 | 0 | 0 | | 32565 | 5071 | boosted | 2014-06-25 | 0 | 0.533333 | 0.533333 | 0 | 0 | | 32567 | 5071 | boosted | 2014-06-26 | 0 | 1.06667 | 1.06667 | 0 | 0 | | 32569 | 5071 | boosted | 2014-06-27 | 0 | 2.1 | 2.1 | 0 | 0 | | 32571 | 5071 | boosted | 2014-06-28 | 0 | 3.13333 | 3.13333 | 0 | 0 | +-------+-----------------+---------+------------+--------------------+-----------------+-----------------+------------------------+-----------------------+ 10 rows in set (0.00 sec) mysql> select sum(if(aseaadb.budget > 0, aseaadb.budget, 0.01)) from tsacommon.accounts a join tsacommon.searchEngineAccounts sea on sea.accountID=a.ID join admaxSearchEngineAccountActualDailyBudgets aseaadb on aseaadb.searchEngineAccountID = sea.ID and aseaadb.date = "2014-06-24" where a.id=81719 group by sea.distributionID; +---------------------------------------------------+ | sum(if(aseaadb.budget > 0, aseaadb.budget, 0.01)) | +---------------------------------------------------+ | 1.03333330154419 | | 1.03333330154419 | +---------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select sum(if(aseaadb.budget > 0, aseaadb.budget, 0.01)) from tsacommon.accounts a join tsacommon.searchEngineAccounts sea on sea.accountID=a.ID join admaxSearchEngineAccountActualDailyBudgets aseaadb on aseaadb.searchEngineAccountID = sea.ID and aseaadb.date = "2014-06-25" where a.id=81719 group by sea.distributionID; +---------------------------------------------------+ | sum(if(aseaadb.budget > 0, aseaadb.budget, 0.01)) | +---------------------------------------------------+ | 1.56666660308838 | | 1.56666660308838 | +---------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select sum(if(aseaadb.budget > 0, aseaadb.budget, 0.01)) from tsacommon.accounts a join tsacommon.searchEngineAccounts sea on sea.accountID=a.ID join admaxSearchEngineAccountActualDailyBudgets aseaadb on aseaadb.searchEngineAccountID = sea.ID and aseaadb.date = "2014-06-26" where a.id=81719 group by sea.distributionID; +---------------------------------------------------+ | sum(if(aseaadb.budget > 0, aseaadb.budget, 0.01)) | +---------------------------------------------------+ | 2.09999990463257 | | 2.09999990463257 | +---------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select sum(if(aseaadb.budget > 0, aseaadb.budget, 0.01)) from tsacommon.accounts a join tsacommon.searchEngineAccounts sea on sea.accountID=a.ID join admaxSearchEngineAccountActualDailyBudgets aseaadb on aseaadb.searchEngineAccountID = sea.ID and aseaadb.date = "2014-06-27" where a.id=81719 group by sea.distributionID; +---------------------------------------------------+ | sum(if(aseaadb.budget > 0, aseaadb.budget, 0.01)) | +---------------------------------------------------+ | 3.05550694465637 | | 3.05550694465637 | +---------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select sum(if(aseaadb.budget > 0, aseaadb.budget, 0.01)) from tsacommon.accounts a join tsacommon.searchEngineAccounts sea on sea.accountID=a.ID join admaxSearchEngineAccountActualDailyBudgets aseaadb on aseaadb.searchEngineAccountID = sea.ID and aseaadb.date = "2014-06-28" where a.id=81719 group by sea.distributionID; +---------------------------------------------------+ | sum(if(aseaadb.budget > 0, aseaadb.budget, 0.01)) | +---------------------------------------------------+ | 3.05550694465637 | | 3.05550694465637 | +---------------------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT round(sum(budget)/30.4,2) from (select distinct ac.ID, sea.distributionID, anmb.budget from admaxCampaigns ac join admaxNominalMonthlyBudgets anmb on anmb.admaxCampaignID = ac.ID and ac.accountID=81719 join admaxSearchEngineAccountMap aseam on aseam.admaxCampaignID = ac.ID and anmb.startDate <= "2014-06-24" and (endDate >= "2014-06-24" or endDate is null) join tsacommon.searchEngineAccounts sea on sea.ID = aseam.searchEngineAccountID) data group by distributionID; +---------------------------+ | round(sum(budget)/30.4,2) | +---------------------------+ | 1.02 | | 1.02 | +---------------------------+ 2 rows in set (0.00 sec) mysql> SELECT round(sum(budget)/30.4,2) from (select distinct ac.ID, sea.distributionID, anmb.budget from admaxCampaigns ac join admaxNominalMonthlyBudgets anmb on anmb.admaxCampaignID = ac.ID and ac.accountID=81719 join admaxSearchEngineAccountMap aseam on aseam.admaxCampaignID = ac.ID and anmb.startDate <= "2014-06-25" and (endDate >= "2014-06-25" or endDate is null) join tsacommon.searchEngineAccounts sea on sea.ID = aseam.searchEngineAccountID) data group by distributionID; +---------------------------+ | round(sum(budget)/30.4,2) | +---------------------------+ | 1.02 | | 1.02 | +---------------------------+ 2 rows in set (0.00 sec) mysql> SELECT round(sum(budget)/30.4,2) from (select distinct ac.ID, sea.distributionID, anmb.budget from admaxCampaigns ac join admaxNominalMonthlyBudgets anmb on anmb.admaxCampaignID = ac.ID and ac.accountID=81719 join admaxSearchEngineAccountMap aseam on aseam.admaxCampaignID = ac.ID and anmb.startDate <= "2014-06-26" and (endDate >= "2014-06-26" or endDate is null) join tsacommon.searchEngineAccounts sea on sea.ID = aseam.searchEngineAccountID) data group by distributionID; +---------------------------+ | round(sum(budget)/30.4,2) | +---------------------------+ | 1.02 | | 1.02 | +---------------------------+ 2 rows in set (0.00 sec) mysql> SELECT round(sum(budget)/30.4,2) from (select distinct ac.ID, sea.distributionID, anmb.budget from admaxCampaigns ac join admaxNominalMonthlyBudgets anmb on anmb.admaxCampaignID = ac.ID and ac.accountID=81719 join admaxSearchEngineAccountMap aseam on aseam.admaxCampaignID = ac.ID and anmb.startDate <= "2014-06-27" and (endDate >= "2014-06-27" or endDate is null) join tsacommon.searchEngineAccounts sea on sea.ID = aseam.searchEngineAccountID) data group by distributionID; +---------------------------+ | round(sum(budget)/30.4,2) | +---------------------------+ | 1.02 | | 1.02 | +---------------------------+ 2 rows in set (0.00 sec) mysql> SELECT round(sum(budget)/30.4,2) from (select distinct ac.ID, sea.distributionID, anmb.budget from admaxCampaigns ac join admaxNominalMonthlyBudgets anmb on anmb.admaxCampaignID = ac.ID and ac.accountID=81719 join admaxSearchEngineAccountMap aseam on aseam.admaxCampaignID = ac.ID and anmb.startDate <= "2014-06-28" and (endDate >= "2014-06-28" or endDate is null) join tsacommon.searchEngineAccounts sea on sea.ID = aseam.searchEngineAccountID) data group by distributionID; +---------------------------+ | round(sum(budget)/30.4,2) | +---------------------------+ | 1.02 | | 1.02 | +---------------------------+ 2 rows in set (0.00 sec)