- working on queries for reports
- required Appropriation/Capabilities combo for 2013
- O&M – ACC, GCC, TRCO
- RDT&E – ACC, CCRSE, GCC, MCC, TRCO
- PROC – GCC, TRCO
- created python script to generate 2013 data – generate_report_data_2013.py
- reworked _view_financial_data to include cumulative committed amounts – DBUpdateEnhancements.031714.sql
- CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `__view_financial_data` AS
select ‘Obligation Goal’ AS `oo_type`,`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,`p`.`program_element` AS `program_element`,
`p`.`facts_pe` AS `facts_pe`,`p`.`sub_center_name` AS `sub_center_name`,`p`.`sub_center_number` AS `sub_center_number`,
`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,
`bc`.`fiscal_year` AS `fiscal_year`,`oo`.`year` AS `year_count`,
((`bc`.`funded_budget` * `oo`.`obligation_month_1`) / 100) AS `month_1`,
((`bc`.`funded_budget` * `oo`.`obligation_month_2`) / 100) AS `month_2`,
((`bc`.`funded_budget` * `oo`.`obligation_month_3`) / 100) AS `month_3`,
((`bc`.`funded_budget` * `oo`.`obligation_month_4`) / 100) AS `month_4`,
((`bc`.`funded_budget` * `oo`.`obligation_month_5`) / 100) AS `month_5`,
((`bc`.`funded_budget` * `oo`.`obligation_month_6`) / 100) AS `month_6`,
((`bc`.`funded_budget` * `oo`.`obligation_month_7`) / 100) AS `month_7`,
((`bc`.`funded_budget` * `oo`.`obligation_month_8`) / 100) AS `month_8`,
((`bc`.`funded_budget` * `oo`.`obligation_month_9`) / 100) AS `month_9`,
((`bc`.`funded_budget` * `oo`.`obligation_month_10`) / 100) AS `month_10`,
((`bc`.`funded_budget` * `oo`.`obligation_month_11`) / 100) AS `month_11`,
((`bc`.`funded_budget` * `oo`.`obligation_month_12`) / 100) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `obligations_outlays_goals` `oo` on((`p`.`appropriation` = `oo`.`appropriation`)))
union
select ‘Outlay Goal’ AS `oo_type`,`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,`p`.`program_element` AS `program_element`,`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,`p`.`sub_center_number` AS `sub_center_number`,`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,`oo`.`year` AS `year_count`,
((`bc`.`funded_budget` * `oo`.`outlay_month_1`) / 100) AS `month_1`,
((`bc`.`funded_budget` * `oo`.`outlay_month_2`) / 100) AS `month_2`,
((`bc`.`funded_budget` * `oo`.`outlay_month_3`) / 100) AS `month_3`,
((`bc`.`funded_budget` * `oo`.`outlay_month_4`) / 100) AS `month_4`,
((`bc`.`funded_budget` * `oo`.`outlay_month_5`) / 100) AS `month_5`,
((`bc`.`funded_budget` * `oo`.`outlay_month_6`) / 100) AS `month_6`,
((`bc`.`funded_budget` * `oo`.`outlay_month_7`) / 100) AS `month_7`,
((`bc`.`funded_budget` * `oo`.`outlay_month_8`) / 100) AS `month_8`,
((`bc`.`funded_budget` * `oo`.`outlay_month_9`) / 100) AS `month_9`,
((`bc`.`funded_budget` * `oo`.`outlay_month_10`) / 100) AS `month_10`,
((`bc`.`funded_budget` * `oo`.`outlay_month_11`) / 100) AS `month_11`,
((`bc`.`funded_budget` * `oo`.`outlay_month_12`) / 100) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `obligations_outlays_goals` `oo` on((`p`.`appropriation` = `oo`.`appropriation`)))
union
select ‘Committed Monthly’ AS `oo_type`,`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,`p`.`program_element` AS `program_element`,`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,`p`.`sub_center_number` AS `sub_center_number`,`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
union
select ‘Committed’ AS `oo_type`,
`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,
`p`.`center_name` AS `center_name`,
`p`.`center_number` AS `center_number`,
`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,
`p`.`capability` AS `capability`,
`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,
`p`.`program_element` AS `program_element`,
`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,
`p`.`sub_center_number` AS `sub_center_number`,
`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 3
group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
UNION
select ‘Committed’ AS `oo_type`,
`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,
`p`.`center_name` AS `center_name`,
`p`.`center_number` AS `center_number`,
`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,
`p`.`capability` AS `capability`,
`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,
`p`.`program_element` AS `program_element`,
`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,
`p`.`sub_center_number` AS `sub_center_number`,
`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 2
group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
UNION
select ‘Committed’ AS `oo_type`,
`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,
`p`.`center_name` AS `center_name`,
`p`.`center_number` AS `center_number`,
`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,
`p`.`capability` AS `capability`,
`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,
`p`.`program_element` AS `program_element`,
`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,
`p`.`sub_center_number` AS `sub_center_number`,
`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 1
group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
UNION
select ‘Committed’ AS `oo_type`,
`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,
`p`.`begin_year` AS `begin_year`,
`p`.`end_year` AS `end_year`,
`p`.`center_name` AS `center_name`,
`p`.`center_number` AS `center_number`,
`p`.`appropriation` AS `appropriation`,
`p`.`type` AS `type`,
`p`.`capability` AS `capability`,
`p`.`expenditure_center` AS `expenditure_center`,
`p`.`investment_portfolio` AS `investment_portfolio`,
`p`.`program_element` AS `program_element`,
`p`.`facts_pe` AS `facts_pe`,
`p`.`sub_center_name` AS `sub_center_name`,
`p`.`sub_center_number` AS `sub_center_number`,
`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,
`bc`.`total_budget` AS `total_budget`,`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,
((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`) + 1) AS `year_count`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) AS `month_1`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) AS `month_2`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) AS `month_3`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) AS `month_4`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) AS `month_5`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) AS `month_6`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) AS `month_7`,
sum(if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0)) AS `month_8`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) AS `month_9`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) AS `month_10`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) AS `month_11`,
sum(if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0)) + sum(if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0)) AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `committed_amounts` `ca` on((`ca`.`budget_center_id` = `bc`.`uid`)))
WHERE ((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) – `bc`.`fiscal_year`)) = 0
group by `ca`.`budget_center_id`,if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`))
union
select if((`oo`.`type` = ‘Reported Outlay $:’),’Outlays’,if((`oo`.`type` = ‘Reported FACTS Obligated $:’),’Obligated’,’PM Actuals’)) AS `oo_type`,
`p`.`project_number` AS `project_number`,`p`.`title` AS `title`,`p`.`begin_year` AS `begin_year`,`p`.`end_year` AS `end_year`,
`p`.`center_name` AS `center_name`,`p`.`center_number` AS `center_number`,`p`.`appropriation` AS `appropriation`,`p`.`type` AS `type`,
`p`.`capability` AS `capability`,`p`.`expenditure_center` AS `expenditure_center`,`p`.`investment_portfolio` AS `investment_portfolio`,
`p`.`program_element` AS `program_element`,`p`.`facts_pe` AS `facts_pe`,`p`.`sub_center_name` AS `sub_center_name`,
`p`.`sub_center_number` AS `sub_center_number`,`bc`.`req_id` AS `req_id`,`bc`.`req_type` AS `req_type`,`bc`.`total_budget` AS `total_budget`,
`bc`.`funded_budget` AS `funded_budget`,`bc`.`fiscal_year` AS `fiscal_year`,`oo`.`year_count` AS `year_count`,
`oo`.`month_1` AS `month_1`,`oo`.`month_2` AS `month_2`,`oo`.`month_3` AS `month_3`,`oo`.`month_4` AS `month_4`,`oo`.`month_5` AS `month_5`,
`oo`.`month_6` AS `month_6`,`oo`.`month_7` AS `month_7`,`oo`.`month_8` AS `month_8`,`oo`.`month_9` AS `month_9`,`oo`.`month_10` AS `month_10`,
`oo`.`month_11` AS `month_11`,`oo`.`month_12` AS `month_12`
from ((`projects` `p` left join `budget_centers` `bc` on((`p`.`uid` = `bc`.`project_id`)))
left join `obligations_outlays` `oo` on(((`p`.`uid` = `oo`.`project_id`) and (`bc`.`uid` = `oo`.`funding_id`))))
where ((`oo`.`type` = ‘Reported Outlay $:’) or (`oo`.`type` = ‘Reported FACTS Obligated $:’) or (`oo`.`type` = ‘PM Actuals (Invoiced) Outlay $:’));
- CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `__view_financial_data` AS
viztales
Dimension reduction, State, Orientation, and Speed
viztales 