Dong Shin 05.15.2013

  • continue working on FA/RA
    • added POC filter for adding service POC’s to Req’s
    • filter Requisitions List in RA per assigned Service POC’s to the Req’s
    • working on getting status for Req’s – done!
      • query to find Overdue, Due, Current Req’s
        • SELECT * FROM (
          SELECT ‘CURRENT’ AS status, c.* FROM budget_centers c WHERE uid NOT IN (
          SELECT funding_id FROM (
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_1) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_2) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_3) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_4) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_5) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_6) GROUP BY year, year_count, funding_id
          ) AS t)
          UNIONSELECT ‘DUE’ AS status, c.* FROM budget_centers c WHERE uid IN (
          SELECT funding_id FROM (
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_1) GROUP BY year, year_count, funding_id
          ) AS t)
          AND uid NOT IN (
          SELECT funding_id FROM (
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_1) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_2) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_3) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_4) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_5) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_6) GROUP BY year, year_count, funding_id
          ) AS t)

          UNION

          SELECT ‘OVERDUE’ AS status, c.* FROM budget_centers c WHERE uid IN (
          SELECT funding_id FROM (
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_1) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_2) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_3) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_4) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_5) GROUP BY year, year_count, funding_id
          UNION
          SELECT count(*) as null_count, funding_id, year, year_count FROM budget_centers c, obligations_outlays o
          WHERE c.uid = o.funding_id AND o.year + o.year_count = 2014 AND ISNULL(month_6) GROUP BY year, year_count, funding_id
          ) AS t
          )
          ) AS t2 ORDER BY uid