Dong Shin 12.12.2013

  • working on queries for pre-processing (COGNOS conflicts)
    • find multiple obligation dates
      SELECT * FROM contracts_cognos WHERE uid IN (SELECT a.uid FROM contracts_cognos a INNER JOIN contracts_cognos b ON a.requisition_id = b.requisition_id AND a.sub_budget_center = b.sub_budget_center AND NOT ISNULL(a.obligation_date) AND NOT ISNULL(b.obligation_date) WHERE a.uid <> b.uid ORDER BY a.uid)
    • Committed Amount greater than Req Allocation
      SELECT * FROM contracts_cognos cco WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id WHERE cco.requisition_id = cc.requisition_id AND cco.sub_budget_center = cc.budget_center AND cco.po_reference = cc.po_reference GROUP BY cc.requisition_id, cc.sub_budget_center, cc.po_reference HAVING SUM(committed_amount) > SUM(total_budget)))
    • Obligated Amount greater than Req Allocation
      SELECT * FROM contracts_cognos cco WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id WHERE cco.requisition_id = cc.requisition_id AND cco.sub_budget_center = cc.budget_center AND cco.po_reference = cc.po_reference GROUP BY cc.requisition_id, cc.sub_budget_center, cc.po_reference HAVING SUM(obligated_amount) > SUM(total_budget)))
      SELECT * FROM contracts_cognos WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id GROUP BY requisition_id, sub_budget_center, po_reference HAVING SUM(obligated_amount) > SUM(total_budget)))
    • Outlay Amount greater than Req Allocation
      SELECT * FROM contracts_cognos cco WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id WHERE cco.requisition_id = cc.requisition_id AND cco.sub_budget_center = cc.budget_center AND cco.po_reference = cc.po_reference GROUP BY cc.requisition_id, cc.sub_budget_center, cc.po_reference HAVING SUM(expenseded_amount) > SUM(total_budget)))
      SELECT * FROM contracts_cognos WHERE FIND_IN_SET (uid, (SELECT GROUP_CONCAT(cc.uid) FROM contracts_cognos cc LEFT JOIN budget_centers AS bc ON SUBSTR(cc.requisition_id, 5) = bc.req_id GROUP BY requisition_id, sub_budget_center, po_reference HAVING SUM(expenseded_amount) > SUM(total_budget)))
    • Obligated Amount greater than Committed Amount
      SELECT * FROM contracts_cognos WHERE obligated_amount > committed_amount
    • Outlay Amount greater than Committed Amount
      SELECT * FROM contracts_cognos WHERE expensed_amount > committed_amount
    • Outlay Amount greater than Obligated Amount
      SELECT * FROM contracts_cognos WHERE expensed_amount > obligated_amount