Dong Shin 06.30.11

  • Find projects that have less than goal in the first month query
    • SELECT p.uid as ‘project_id’, p.project_number, b.uid as ‘budget_center_id’, b.center_number, b.appropriation, a.year as ‘budget_year’,
      REPLACE (FORMAT(a.amount,2),’,’,”) as amount, o.uid as ‘obligation_outlay_id’,
      o.year_count,
      o.year as ‘obligation_outlay_year’,
      o.type,
      REPLACE (FORMAT(o.month_1, 2),’,’,”) as month_1,
      REPLACE (FORMAT(IF (o.type like ‘%Outlay%’, (g.outlay_month_1 * a.amount / 100), (g.obligation_month_1 * a.amount) / 100), 2),’,’,”) as goal_month_1,
      IF (o.month_1 < IF (o.type like ‘%Outlay%’, (g.outlay_month_1 * a.amount / 100), (g.obligation_month_1 * a.amount) / 100), ‘FLAG’, ‘OK’) as flag_month_1
      FROM project_portfolio.projects p, project_portfolio.budget_centers b, project_portfolio.budget_amounts a, project_portfolio.obligations_outlays o, project_portfolio.obligations_outlays_goals g
      WHERE p.uid = b.project_id AND b.uid = a.budget_center_id AND b.uid = o.funding_id AND b.appropriation = g.appropriation AND g.year = year_count AND a.year = g.year
      AND o.month_1 < IF (o.type like ‘%Outlay%’, (g.outlay_month_1 * a.amount / 100), (g.obligation_month_1 * a.amount) / 100)
      GROUP BY project_id
      ORDER BY b.uid, a.year, o.year