Dong Shin 05.21.2012

  • decided to use SQL query to genenrate COGNOS supporting data (PM Actuals) instead of a lengthy Python script
  • working on SQL queries…
  • COGNOS with matching PM Actuals
    • SELECT f.*,
      CASE WHEN SUBSTR(FY_Month, 8, 3) = ‘Oct’ THEN  month_1
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Nov’ THEN  month_2
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Dec’ THEN  month_3
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jan’ THEN  month_4
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Feb’ THEN  month_5
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Mar’ THEN  month_6
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Apr’ THEN  month_7
      WHEN SUBSTR(FY_Month, 8, 3) = ‘May’ THEN  month_8
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jun’ THEN  month_9
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jul’ THEN  month_10
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Aug’ THEN  month_11
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Sep’ THEN  month_12
      ELSE ‘other’
      END AS PM_Actuals
      FROM visibility_dev2.facts_fy12 f, project_portfolio.budget_centers c, project_portfolio.obligations_outlays o
      WHERE c.uid = o.funding_id
      AND f.Program = c.facts_pe
      AND f.BC = c.center_number
      AND f.Approp = c.appropriation
      AND o.year = SUBSTR(FY_Month, 3, 4)
      AND o.year_count = 1
      AND o.type LIKE “PM Actuals%”
      ORDER BY f.uid
  • match COGNOS data with PPM data
    • SELECT f.*, o.month_1 as PM_Actuals FROM visibility_dev2.facts_fy12 f, project_portfolio.budget_centers c, project_portfolio.obligations_outlays o
      WHERE c.uid = o.funding_id
      AND f.Program = c.facts_pe
      AND f.BC = c.center_number
      AND f.Approp = c.appropriation
      AND o.year = 2012
      AND o.year_count = 1
      AND o.type LIKE “PM Actuals%”
  • parse FY_Month field for year and month
    • SELECT f.FY_Month, SUBSTR(FY_Month, 3, 4) as year,
      CASE WHEN SUBSTR(FY_Month, 8, 3) = ‘Oct’ THEN  ‘month_1’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Nov’ THEN  ‘month_2’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Dec’ THEN  ‘month_3’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jan’ THEN  ‘month_4’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Feb’ THEN  ‘month_5’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Mar’ THEN  ‘month_6’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Apr’ THEN  ‘month_7’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘May’ THEN  ‘month_8’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jun’ THEN  ‘month_9’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Jul’ THEN  ‘month_10’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Aug’ THEN  ‘month_11’
      WHEN SUBSTR(FY_Month, 8, 3) = ‘Sep’ THEN  ‘month_12’
      ELSE ‘other’
      END AS month
      FROM visibility_dev2.facts_fy12 f