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 | +-------+-----------------------+------------+----------+ | 64703 | 595596 | 2014-06-24 | 0.516667 | | 64704 | 595597 | 2014-06-24 | 0.516667 | | 64705 | 595598 | 2014-06-24 | 0.516667 | | 64706 | 595599 | 2014-06-24 | 0.516667 | | 64710 | 595599 | 2014-06-25 | 0.516667 | | 64709 | 595598 | 2014-06-25 | 0.516667 | | 64708 | 595597 | 2014-06-25 | 0.783333 | | 64707 | 595596 | 2014-06-25 | 0.783333 | | 64714 | 595599 | 2014-06-26 | 0.516667 | | 64713 | 595598 | 2014-06-26 | 0.516667 | | 64712 | 595597 | 2014-06-26 | 1.05 | | 64711 | 595596 | 2014-06-26 | 1.05 | | 64718 | 595599 | 2014-06-27 | 0.516667 | | 64717 | 595598 | 2014-06-27 | 0.516667 | | 64716 | 595597 | 2014-06-27 | 1.52775 | | 64715 | 595596 | 2014-06-27 | 1.52775 | | 64719 | 595596 | 2014-06-28 | 1.52775 | | 64720 | 595597 | 2014-06-28 | 1.52775 | | 64721 | 595598 | 2014-06-28 | 0.516667 | | 64722 | 595599 | 2014-06-28 | 0.516667 | +-------+-----------------------+------------+----------+ 20 rows in set (0.02 sec) mysql> select * from `st-tracker`.admaxActualDailyBudgets where admaxCampaignID in(5070,5071); +-------+-----------------+---------+------------+---------+ | id | admaxCampaignID | type | date | budget | +-------+-----------------+---------+------------+---------+ | 32552 | 5070 | boosted | 2014-06-24 | 1.03333 | | 32554 | 5070 | boosted | 2014-06-25 | 1.56667 | | 32556 | 5070 | boosted | 2014-06-26 | 2.1 | | 32558 | 5070 | boosted | 2014-06-27 | 3.13333 | | 32560 | 5070 | boosted | 2014-06-28 | 4.16667 | | 32553 | 5071 | boosted | 2014-06-24 | 1.03333 | | 32555 | 5071 | boosted | 2014-06-25 | 1.56667 | | 32557 | 5071 | boosted | 2014-06-26 | 2.1 | | 32559 | 5071 | boosted | 2014-06-27 | 3.13333 | | 32561 | 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 | +-------+-----------------+---------+------------+--------------------+-----------------+-----------------+------------------------+-----------------------+ | 32552 | 5070 | boosted | 2014-06-24 | 0 | 0 | 0 | 0 | 0 | | 32554 | 5070 | boosted | 2014-06-25 | 0 | 0.533333 | 0.533333 | 0 | 0 | | 32556 | 5070 | boosted | 2014-06-26 | 0 | 1.06667 | 1.06667 | 0 | 0 | | 32558 | 5070 | boosted | 2014-06-27 | 0 | 2.1 | 2.1 | 0 | 0 | | 32560 | 5070 | boosted | 2014-06-28 | 0 | 3.13333 | 3.13333 | 0 | 0 | | 32553 | 5071 | boosted | 2014-06-24 | 0 | 0 | 0 | 0 | 0 | | 32555 | 5071 | boosted | 2014-06-25 | 0 | 0.533333 | 0.533333 | 0 | 0 | | 32557 | 5071 | boosted | 2014-06-26 | 0 | 1.06667 | 1.06667 | 0 | 0 | | 32559 | 5071 | boosted | 2014-06-27 | 0 | 2.1 | 2.1 | 0 | 0 | | 32561 | 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.02591776847839 | +---------------------------------------------------+ 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 | | 1.02591776847839 | +---------------------------------------------------+ 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 | | 1.02591776847839 | +---------------------------------------------------+ 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 | | 1.0185022354126 | +---------------------------------------------------+ 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)