Dong Shin 03.21.2014

  • need to remove current month from the queries for the reports/presencation
  • tried to create view, __view_financial_data, only to find out that variables are *NOT* allowed! Ugh, need to find a workaround.
    • created a trigger –  update_tmp_view_financial_data  to update __tmp_view_financial_data on obligations/outlays update. this deletes all entries and regenerates financial data whenever obligations/outlays are updated….
      • CREATE DEFINER=`root`@`localhost` TRIGGER `update_tmp_view_financial_data` AFTER UPDATE ON `obligations_outlays` FOR EACH ROW begin delete from __tmp_view_financial_data; call generate_financial_data(); end
    • created stored procedure – generate_financial_data()
    • created a table – __tmp_view_financial_data from the query in DBUpdateEnhancements.032114.sql
  • working on refining queries for financial data
    • currently takes ~8 seconds for the query
    • optimized to 0.5 using user defined variable for year_total
      • select ‘Committed’ AS oo_type, project_number, title, begin_year, end_year, center_name, center_number,
        appropriation, type, capability, expenditure_center, investment_portfolio, program_element,
        facts_pe, sub_center_name, sub_center_number, req_id, req_type, total_budget, funded_budget,
        fiscal_year, year_count,
        @year_total := IFNULL((SELECT SUM(year_total) FROM __tmp_monthly_committed vmc2 WHERE vmc1.req_id = vmc2.req_id
        AND year_count < 4 GROUP BY vmc2.req_id),0) as year_total ,
        @year_total + month_1 AS month_1,
        @year_total + month_1+month_2 AS month_2,
        @year_total + month_1+month_2+month_3 AS month_3,
        @year_total + month_1+month_2+month_3+month_4 AS month_4,
        @year_total + month_1+month_2+month_3+month_4+month_5 AS month_5,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6 AS month_6,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7 AS month_7,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8 AS month_8,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9 AS month_9,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9+month_10 AS month_10,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9+month_10+month_11 AS month_11,
        @year_total + month_1+month_2+month_3+month_4+month_5+month_6+month_7+month_8+month_9+month_10+month_11+month_12 AS month_12
        FROM __tmp_monthly_committed vmc1
        WHERE year_count = 4