Dong Shin 11.28.2011

  • revised Budget Details Query so that the navigation is bit easier….
    • SELECT c.*, a.uid as a_uid, a.amount as amount, o.year, o.year_count, p.duration,
      IF(SUM(IF(ISNULL(month_1), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_1), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_1_status,
      IF(SUM(IF(ISNULL(month_2), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_2), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_2_status,
      IF(SUM(IF(ISNULL(month_3), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_3), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_3_status,
      IF(SUM(IF(ISNULL(month_4), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_4), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_4_status,
      IF(SUM(IF(ISNULL(month_5), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_5), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_5_status,
      IF(SUM(IF(ISNULL(month_6), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_6), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_6_status,
      IF(SUM(IF(ISNULL(month_7), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_7), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_7_status,
      IF(SUM(IF(ISNULL(month_8), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_8), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_8_status,
      IF(SUM(IF(ISNULL(month_9), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_9), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_9_status,
      IF(SUM(IF(ISNULL(month_10), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_10), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_10_status,
      IF(SUM(IF(ISNULL(month_11), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_11), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_11_status,
      IF(SUM(IF(ISNULL(month_12), 1, 0)) >= 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(month_12), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as month_12_status
      FROM budget_centers c, obligations_outlays o, budget_amounts a, appropriations p
      WHERE c.project_id = 173
      AND c.uid = o.funding_id
      AND a.budget_center_id = c.uid
      AND a.year = o.year_count
      AND c.appropriation = p.type
      AND o.type <> ‘Reported Outlay $:’ AND o.type <> ‘Outlay $ (Reported in FACTS)’
      GROUP BY o.year, o.year_count
      ORDER BY c.uid, o.year, o.year_count
  • looks like a separate list of current months’ status is needed to handle all/current months navigation…
    • query
      • SELECT o.year, o.year_count, o.project_id
        IF(SUM(IF(ISNULL(o.month_2), 1, 0)) = 4, ‘OVERDUE’, IF(SUM(IF(ISNULL(o.month_2), 1, 0)) > 0, ‘INCOMPLETE’, ‘CURRENT’)) as status
        FROM obligations_outlays o
        WHERE o.project_id=172
        AND (o.year + o.year_count) = 2013
        AND o.type <> ‘Reported Outlay $:’ AND o.type <> ‘Outlay $ (Reported in FACTS)’
        GROUP BY year, year_count