Dong Shin 03.19.2014

  • deployed new FA/queries
    • found that committed amounts are not generated when there is no data for the year – fixed
    • fixed various errors from Lenny
  • ReqDataAnalysis now has combination of bar and area just like the presentation…
  • new query slows down the __view_financial_data…
  • new __view_monthly_committed view
    • CREATE ALGORITHM = UNDEFINED VIEW `__view_monthly_committed` AS
      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`,
      o.year_count AS `year_count`,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 10),`ca`.`committed_amount`,0), 0)) as month_1,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 11),`ca`.`committed_amount`,0), 0)) as month_2,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 12),`ca`.`committed_amount`,0), 0)) as month_3,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 1),`ca`.`committed_amount`,0), 0)) as month_4,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 2),`ca`.`committed_amount`,0), 0)) as month_5,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 3),`ca`.`committed_amount`,0), 0)) as month_6,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 4),`ca`.`committed_amount`,0), 0)) as month_7,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 5),`ca`.`committed_amount`,0), 0)) as month_8,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 6),`ca`.`committed_amount`,0), 0)) as month_9,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 7),`ca`.`committed_amount`,0), 0)) as month_10,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 8),`ca`.`committed_amount`,0), 0)) as month_11,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), if((month(`ca`.`committed_date`) = 9),`ca`.`committed_amount`,0), 0)) as month_12,
      SUM(IF((if((month(`ca`.`committed_date`) >= 10),(year(`ca`.`committed_date`) + 1),year(`ca`.`committed_date`)) = (fiscal_year + o.year_count – 1)), `ca`.`committed_amount`, 0)) as year_total
      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`)))
      left join obligations_outlays o on p.uid = o.project_id AND o.funding_id = bc.uid AND o.type = ‘Reported Outlay $:’
      group by `ca`.`budget_center_id`,o.year_count;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.