Dong Shin 11.22.2011

  • pulling my hair over SQL queries……….
  • get status of selected project by year and appropriation year
    • SELECT c.*, a.uid as a_uid, a.amount as amount, o.year, o.year_count,
      IF (
      (((SUM(IF(ISNULL(month_1), 1, 0)) = 6 OR SUM(IF(ISNULL(month_2), 1, 0)) = 6)
      AND o.year+year_count=2013) OR
      ((SUM(IF(ISNULL(month_1), 1, 0)) = 6 OR SUM(IF(ISNULL(month_2), 1, 0)) = 6 OR
      SUM(IF(ISNULL(month_3), 1, 0)) = 6 OR SUM(IF(ISNULL(month_4), 1, 0)) = 6 OR
      SUM(IF(ISNULL(month_5), 1, 0)) = 6 OR SUM(IF(ISNULL(month_6), 1, 0)) = 6 OR
      SUM(IF(ISNULL(month_7), 1, 0)) = 6 OR SUM(IF(ISNULL(month_8), 1, 0)) = 6 OR
      SUM(IF(ISNULL(month_9), 1, 0)) = 6 OR SUM(IF(ISNULL(month_10), 1, 0)) = 6 OR
      SUM(IF(ISNULL(month_11), 1, 0)) = 6 OR SUM(IF(ISNULL(month_12), 1, 0)) = 6)
      AND o.year+year_count<2013))
      ,’OVERDUE’,
      IF (
      (((SUM(IF(ISNULL(month_2), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_2), 1, 0)) > 0) AND o.year+year_count=2013) OR
      ((SUM(IF(ISNULL(month_1), 1, 0)) > 0 OR SUM(IF(ISNULL(month_2), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_3), 1, 0)) > 0 OR SUM(IF(ISNULL(month_4), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_5), 1, 0)) > 0 OR SUM(IF(ISNULL(month_6), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_7), 1, 0)) > 0 OR SUM(IF(ISNULL(month_8), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_9), 1, 0)) > 0 OR SUM(IF(ISNULL(month_10), 1, 0)) > 0 OR
      SUM(IF(ISNULL(month_11), 1, 0)) > 0 OR SUM(IF(ISNULL(month_12), 1, 0)) > 0)
      AND o.year+year_count<2013))
      , ‘INCOMPLETE’, ‘CURRENT’))
      as status
      FROM budget_centers c, obligations_outlays o, budget_amounts a
      WHERE o.project_id = 171
      AND c.uid = o.funding_id
      AND a.budget_center_id = c.uid AND a.year = year_count
      GROUP BY o.year, year_count
  • get status of project  for year 2012 and month 2
    • SELECT p.*,
      IF(p.uid IN
      (SELECT project_id FROM
      (SELECT project_id, year, year_count,
      count(funding_id) as data_count
      FROM obligations_outlays WHERE
      ((ISNULL(month_1) OR ISNULL(month_2))
      AND (year+year_count) = 2013)
      OR
      ((ISNULL(month_1) OR ISNULL(month_2) OR
      ISNULL(month_3) OR ISNULL(month_4) OR
      ISNULL(month_5) OR ISNULL(month_6) OR
      ISNULL(month_7) OR ISNULL(month_8) OR
      ISNULL(month_9) OR ISNULL(month_10) OR
      ISNULL(month_11) OR ISNULL(month_12))

      AND (year+year_count) < 2013)
      GROUP BY funding_id, year, year_count
      ) AS FOO
      WHERE data_count = 6), ‘OVERDUE’,
      IF(p.uid IN
      (SELECT project_id FROM
      (SELECT project_id, year, year_count,
      count(funding_id) as data_count
      FROM obligations_outlays WHERE
      ((ISNULL(month_1) OR ISNULL(month_2))
      AND (year+year_count) = 2013)
      OR
      ((ISNULL(month_1) OR ISNULL(month_2) OR
      ISNULL(month_3) OR ISNULL(month_4) OR
      ISNULL(month_5) OR ISNULL(month_6) OR
      ISNULL(month_7) OR ISNULL(month_8) OR
      ISNULL(month_9) OR ISNULL(month_10) OR
      ISNULL(month_11) OR ISNULL(month_12))

      AND (year+year_count) < 2013)
      GROUP BY funding_id, year, year_count
      ) AS FOO
      WHERE data_count < 6), ‘INCOMPLETE’, ‘CURRENT’)
      ) as status
      FROM projects p