Dong Shin 10.22.2012

  • working on queries to calculate actual performance of Financial data
  • created a script to generate alerts for planned values that are out of acceptable range (+/- 5%) – AlertPlannedValues.py
  • query to find planned values under or over 5%
    • SELECT p.uid, c.uid, c.center_number, c.appropriation,
      a.year, a.amount,
      o.uid, o.type, o.year, o.year_count, o.month_1,
      g.uid,
      @goal_month_1 := IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) as goal_month_1,
      TRUNCATE(a.amount * @goal_month_1 / 100, 2) as goal_month_1_amount,
      @goal_month_1_min := TRUNCATE(a.amount * @goal_month_1 / 100, 2) * 0.95 as goal_month_1_min,
      @goal_month_1_max := TRUNCATE(a.amount * @goal_month_1 / 100, 2) * 1.05 as goal_month_1_max
      FROM projects p, budget_centers c, budget_amounts a, obligations_outlays o, obligations_outlays_goals g
      WHERE p.uid = 176
      AND p.uid = c.project_id
      AND a.budget_center_id = c.uid
      AND o.project_id = p.uid
      AND o.funding_id = c.uid
      AND g.appropriation = c.appropriation
      AND g.year = o.year_count
      AND (ISNULL(o.month_1) OR (o.month_1 > TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 1.05
      AND o.month_1 <  TRUNCATE(a.amount *  IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) / 100, 2) * 0.95))
      AND o.type = ‘Planned Obligated $:’
  • query to calculate goals and actuals
    • SELECT p.uid, c.uid, c.center_number, c.appropriation,
      a.year, a.amount,
      o.uid, o.type, o.year, o.year_count, o.month_1,
      g.uid,
      @goal_month_1 := IF(o.type LIKE ‘%Obligate%’,g.obligation_month_1, g.outlay_month_1) as goal_month_1,
      TRUNCATE(a.amount * @goal_month_1 / 100, 2) as goal_month_1_amount
      FROM projects p, budget_centers c, budget_amounts a, obligations_outlays o, obligations_outlays_goals g
      WHERE p.uid = 176
      AND p.uid = c.project_id
      AND a.budget_center_id = c.uid
      AND o.project_id = p.uid
      AND o.funding_id = c.uid
      AND g.appropriation = c.appropriation
      AND g.year = o.year_count