Dong Shin 03.06.2014

  • trying to figure out if months can be updated in a single query……. instead of separate queries for each month!
    • UPDATE obligations_outlays oo
      SET month_12 = CASE
      WHEN (month_11 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_11
      WHEN (month_10 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_10
      WHEN (month_9 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_9
      WHEN (month_8 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_8
      WHEN (month_7 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_7
      WHEN (month_6 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_6
      WHEN (month_5 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_5
      WHEN (month_4 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_4
      WHEN (month_3 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_3
      WHEN (month_2 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_2
      WHEN (month_1 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_1
      ELSE month_12 END,
      month_11 = CASE
      …………………….
      month_2 = CASE
      WHEN (month_1 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_1
      ELSE month_2 END
      WHERE oo.year_count = 1
      AND (oo.type = ‘Outlay $ (Reported in FACTS)’ OR oo.type = ‘Reported FACTS Obligated $:’)
  • finally got the query working! – that update first month second year when last month first year is at 100%
    • UPDATE obligations_outlays oo1
      LEFT JOIN obligations_outlays oo2
      ON (oo1.project_id = oo2.project_id
      AND oo1.funding_id = oo2.funding_id
      AND oo1.type = oo2.type
      AND oo1.uid <> oo2.uid)
      LEFT JOIN budget_centers bc
      ON( oo1.funding_id = bc.uid)
      AND (oo1.type = ‘Outlay $ (Reported in FACTS)’ OR oo1.type = ‘Reported FACTS Obligated $:’)
      AND (oo2.month_12 >= bc.total_budget)
      SET oo1.month_1 = oo2.month_12
      WHERE oo1.year_count = 2 AND oo2.year_count = 1
      AND (oo1.type = ‘Outlay $ (Reported in FACTS)’ OR oo1.type = ‘Reported FACTS Obligated $:’)
  • added months updates queries tor year 2 in Post Processing
  • talked Kelley yesterday and passed along the contact info, she said she would email me the details, never did
    • emailed Kelley this morning and got response, forward it to Chris