Dong Shin 03.24.2014

  • reworked all stored queries for briefing data to have 5 months, fixed month_2 typos – user_queries.sql
  • removed year 3 and 4 to speed up __view_financial_data view – DBUpdateEnhancements.032414.sql
  • created queries for Lenny – summary of FY13 and FY14 by appropriation and capability
    • FY13 Summary by Appropriation and Capability, FY14 Summary by Appropriation and Capability
    • select capability, appropriation,
      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, bc.total_budget, 0)) AS total_budget,
      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, bc.funded_budget, 0)) AS funded_budget,
      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, oo.month_5, 0)) AS ‘Obligated Feb 2013’,

      SUM(IF(oo.type = ‘Outlay $ (Reported in FACTS)’, oo.month_5, 0)) AS ‘Outlay Feb 2013’,

      SUM(IF(oo.type = ‘PM Actuals (Invoiced) Outlay $:’, oo.month_5, 0)) AS ‘PM Actuals Feb 2013’,

      SUM(IF(oo.type = ‘Reported FACTS Obligated $:’, committed_amount,0 )) AS total_committed
      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
      LEFT JOIN __view_committed_totals vct ON bc.uid = vct.budget_center_id
      where fiscal_year = 2013 AND oo.year_count = 2 AND
      (oo.type = ‘Outlay $ (Reported in FACTS)’ OR
      oo.type = ‘Reported FACTS Obligated $:’ OR
      oo.type = ‘PM Actuals (Invoiced) Outlay $:’)
      GROUP BY capability, appropriation