Dong Shin 09.23.2013

  • working on Query Builder
    • working on query to retrieve Obligation/Outlay % values
    • corrected joining tables to get unique obligation/outlay entries…
      • SELECT * FROM
        obligations_outlays o
        LEFT JOIN budget_centers AS bc ON o.funding_id = bc.uid
        LEFT JOIN projects AS p ON o.project_id = p.uid
        LEFT JOIN obligations_outlays_goals g ON g.appropriation = p.appropriation AND g.year = o.year_count
        LEFT JOIN __view_project_totals AS t ON t.project_id = p.uid
        LEFT JOIN __view_budget_center_totals AS t3 ON t3.budget_center = p.center_name
        LEFT JOIN __view_sub_budget_center_totals AS t2 ON t2.sub_budget_center_number = bc.sub_budget_center_number
        LEFT JOIN
        ( SELECT budget_center_id, sub_budget_center, sub_budget_center_name, executing_budget_center, ebc_name, appropriation_year, requisition_id,
        po_reference, acr, MAX(committed_date), SUM(committed_amount), MAX(obligation_date), SUM(obligated_amount), MAX(expensed_date),
        SUM(expensed_amount), MAX(po_start_date), MAX(po_end_date), GROUP_CONCAT(DISTINCT contract_type), GROUP_CONCAT(DISTINCT po_type_code),
        GROUP_CONCAT(contract_no), GROUP_CONCAT(DISTINCT vendor_id) FROM budget_center_contracts GROUP BY budget_center_id ) AS bcc ON bcc.budget_center_id = bc.uid
    • fixed queries using the new joining tables