Dong Shin 03.13.2014

  • deployed new FA, queries
  • updated committed_amounts to be cumulative in __view_financial_data
  • query to update chart data!
    • Insert new row with string replace…
      INSERT INTO table_dashboards (id, name, owner, xml_layout, published)
      SELECT null, name, owner, REPLACE(CONVERT(xml_layout USING utf8),’2013_RDTE_GCC’,’2014_RDTE_GCC’), published FROM table_dashboards
      WHERE id = 1
    • update a row using string replace…..
      UPDATE table_dashboards SET xml_layout=BINARY(REPLACE(CONVERT(xml_layout USING utf8),’2014_RDTE_GCC’,’2015_RDTE_GCC’))
      WHERE id = 2
  • query for committed amounts
    • SELECT bc.uid,bc.fiscal_year,
      YEAR(ca.committed_date)-bc.fiscal_year+1 AS year_count,
      SUM(IF(MONTH(committed_date)=1,committed_amount,0)) AS month_1,
      SUM(IF(MONTH(committed_date)<=2, committed_amount,0)) AS month_2,
      SUM(IF(MONTH(committed_date)<=3, committed_amount,0)) AS month_3,
      SUM(IF(MONTH(committed_date)<=4, committed_amount,0)) AS month_4,
      SUM(IF(MONTH(committed_date)<=5, committed_amount,0)) AS month_5,
      SUM(IF(MONTH(committed_date)<=6, committed_amount,0)) AS month_6,
      SUM(IF(MONTH(committed_date)<=7, committed_amount,0)) AS month_7,
      SUM(IF(MONTH(committed_date)<=8, committed_amount,0)) AS month_8,
      SUM(IF(MONTH(committed_date)<=9, committed_amount,0)) AS month_9,
      SUM(IF(MONTH(committed_date)<=10, committed_amount,0)) AS month_10,
      SUM(IF(MONTH(committed_date)<=11, committed_amount,0)) AS month_11,
      SUM(IF(MONTH(committed_date)<=12, committed_amount,0)) AS month_12,
      SUM(committed_amount),
      YEAR(ca.committed_date),
      MONTH(ca.committed_date)
      FROM budget_centers bc
      LEFT JOIN committed_amounts ca
      ON bc.uid = ca.budget_center_id