4 Replies Latest reply: Dec 6, 2012 4:13 AM by Rahul_India RSS

    Modeling months by years

    ltps
      Hello, everyone - I am new to the SQL model clause and have succeeded with some basics but am struggling to get to the next stage of learning with a requirement to list sums by month (across) and years (down), as in a spreadsheet.

      I have dummied up some data in the WITH clause below. The dummy date is limited to a single year and cost center (for real, I will have multiple years and multiple cost centers, but I think I would be able to handle that). The goal is to list years as rows and months as columns with amounts in the cells. The SQL below works, but it feels wrong, and worse like I don't even understand the rules construction. The DISTINCT especially feels like a cheat. Here is the desired result (for one year only, ignoring the one cost center, for now):


      YEAR     JAN     FEB     MAR     APR     MAY     JUN     JUL     AUG     SEP     OCT     NOV     DEC
      2012     100     300     600     1000     1500     2100     2800     3600     4500     5500     6600     7800


      On one topic in particular I would really like to get some clarification: with my real data (replacing the dummy data in the WITH clause), I have use windowing to create a running sum by month, which is the result I seek in the spreadsheet presentation, going across rather than down. Is this the wrong approach altogether? Should the accumulating sum rather be handled in the modeling rules?

      Go ahead, tell me how dumb my attempt is, I can take it and learn! Thank you for your help.


      WITH t AS (
      SELECT 2012 AS period_year, 1234 AS cost_center, 'JAN' AS period_month, 1 AS period_number, 100 AS month_amount, 100 AS cumulative_amount FROM dual UNION
      SELECT 2012 AS period_year, 1234 AS cost_center, 'FEB' AS period_month, 2 AS period_number, 200 AS month_amount, 300 AS cumulative_amount FROM dual UNION
      SELECT 2012 AS period_year, 1234 AS cost_center, 'MAR' AS period_month, 3 AS period_number, 300 AS month_amount, 600 AS cumulative_amount FROM dual UNION
      SELECT 2012 AS period_year, 1234 AS cost_center, 'APR' AS period_month, 4 AS period_number, 400 AS month_amount, 1000 AS cumulative_amount FROM dual UNION
      SELECT 2012 AS period_year, 1234 AS cost_center, 'MAY' AS period_month, 5 AS period_number, 500 AS month_amount, 1500 AS cumulative_amount FROM dual UNION
      SELECT 2012 AS period_year, 1234 AS cost_center, 'JUN' AS period_month, 6 AS period_number, 600 AS month_amount, 2100 AS cumulative_amount FROM dual UNION
      SELECT 2012 AS period_year, 1234 AS cost_center, 'JUL' AS period_month, 7 AS period_number, 700 AS month_amount, 2800 AS cumulative_amount FROM dual UNION
      SELECT 2012 AS period_year, 1234 AS cost_center, 'AUG' AS period_month, 8 AS period_number, 800 AS month_amount, 3600 AS cumulative_amount FROM dual UNION
      SELECT 2012 AS period_year, 1234 AS cost_center, 'SEP' AS period_month, 9 AS period_number, 900 AS month_amount, 4500 AS cumulative_amount FROM dual UNION
      SELECT 2012 AS period_year, 1234 AS cost_center, 'OCT' AS period_month, 10 AS period_number, 1000 AS month_amount, 5500 AS cumulative_amount FROM dual UNION
      SELECT 2012 AS period_year, 1234 AS cost_center, 'NOV' AS period_month, 11 AS period_number, 1100 AS month_amount, 6600 AS cumulative_amount FROM dual UNION
      SELECT 2012 AS period_year, 1234 AS cost_center, 'DEC' AS period_month, 12 AS period_number, 1200 AS month_amount, 7800 AS cumulative_amount FROM dual
      ORDER BY period_number
      )
      SELECT DISTINCT period_year
      , JAN
      , FEB
      , MAR
      , APR
      , MAY
      , JUN
      , JUL
      , AUG
      , SEP
      , OCT
      , NOV
      , DEC
      FROM t
      MODEL
      PARTITION BY (period_year)
      DIMENSION BY (period_month)
      MEASURES ( cumulative_amount
      , 0 AS JAN
      , 0 AS FEB
      , 0 AS MAR
      , 0 AS APR
      , 0 AS MAY
      , 0 AS JUN
      , 0 AS JUL
      , 0 AS AUG
      , 0 AS SEP
      , 0 AS OCT
      , 0 AS NOV
      , 0 AS DEC
      )
      RULES (
      JAN[ANY] = SUM(cumulative_amount)['JAN']
      ,FEB[ANY] = SUM(cumulative_amount)['FEB']
      ,MAR[ANY] = SUM(cumulative_amount)['MAR']
      ,APR[ANY] = SUM(cumulative_amount)['APR']
      ,MAY[ANY] = SUM(cumulative_amount)['MAY']
      ,JUN[ANY] = SUM(cumulative_amount)['JUN']
      ,JUL[ANY] = SUM(cumulative_amount)['JUL']
      ,AUG[ANY] = SUM(cumulative_amount)['AUG']
      ,SEP[ANY] = SUM(cumulative_amount)['SEP']
      ,OCT[ANY] = SUM(cumulative_amount)['OCT']
      ,NOV[ANY] = SUM(cumulative_amount)['NOV']
      ,DEC[ANY] = SUM(cumulative_amount)['DEC']
      )
      ORDER BY period_year
        • 1. Pivot
          Frank Kulash
          Hi,
          ltps wrote:
          Hello, everyone - I am new to the SQL model clause and have succeeded with some basics but am struggling to get to the next stage of learning with a requirement to list sums by month (across) and years (down), as in a spreadsheet.

          I have dummied up some data in the WITH clause below. The dummy date is limited to a single year and cost center (for real, I will have multiple years and multiple cost centers, but I think I would be able to handle that). The goal is to list years as rows and months as columns with amounts in the cells. The SQL below works, but it feels wrong, and worse like I don't even understand the rules construction. The DISTINCT especially feels like a cheat. Here is the desired result (for one year only, ignoring the one cost center, for now):
          I think the reason this feels awkward to you is that MODEL is the wrong tool for this job.
          MODEL is good when you have to compute the values in one cell from values in other cells. In this problem, you already have all the data computed, you just need to rearrange the items, taking a column from 12 rows and displaying that as 12 columns on 1 row. That's called Pivoting .

          In Oracle 8.1 (and higher) you can pivot the data this way:
          SELECT       cost_center, period_year
          ,       MAX (CASE WHEN period_month = 'JAN' THEN cumulative_amount END)     AS jan
          ,       MAX (CASE WHEN period_month = 'FEB' THEN cumulative_amount END)     AS feb
          ,       MAX (CASE WHEN period_month = 'MAR' THEN cumulative_amount END)     AS mar
          --       ...
          ,       MAX (CASE WHEN period_month = 'DEC' THEN cumulative_amount END)     AS dec
          FROM       t
          GROUP BY  cost_center, period_year
          ORDER BY  cost_center, period_year
          ;
          Starting in Oracle 11.1, you can also use the SELECT ... PIVOT feature.
          YEAR     JAN     FEB     MAR     APR     MAY     JUN     JUL     AUG     SEP     OCT     NOV     DEC
          2012     100     300     600     1000     1500     2100     2800     3600     4500     5500     6600     7800


          On one topic in particular I would really like to get some clarification: with my real data (replacing the dummy data in the WITH clause), I have use windowing to create a running sum by month, which is the result I seek in the spreadsheet presentation, going across rather than down. Is this the wrong approach altogether? Should the accumulating sum rather be handled in the modeling rules?
          Even if MODEL were the right tool for this job, I'm not sure you'd want to do that in the MODEL clause.
          Go ahead, tell me how dumb my attempt is, I can take it and learn! Thank you for your help.
          Thanks for posting the sample data and results; that helps a lot.
          WITH t AS (
          SELECT 2012 AS period_year, 1234 AS cost_center, 'JAN' AS period_month, 1 AS period_number, 100 AS month_amount, 100 AS cumulative_amount FROM dual UNION
          Storing date information in NUMBER and VARCHAR2 columns isn't a very good idea. Use a siungle DATE column instead of period_year and period_month. For one thing, it will make computing cumulative_amount from month_amount much simpler and more efficient.
          SELECT 2012 AS period_year, 1234 AS cost_center, 'DEC' AS period_month, 12 AS period_number, 1200 AS month_amount, 7800 AS cumulative_amount FROM dual
          ORDER BY period_number
          There's no point in having an ORDER BY clause in this sub-query.
          )
          SELECT DISTINCT period_year
          , JAN
          , FEB
          , MAR
          , APR
          , MAY
          , JUN
          , JUL
          , AUG
          , SEP
          , OCT
          , NOV
          , DEC
          FROM t
          MODEL
          PARTITION BY (period_year)
          DIMENSION BY (period_month)
          MEASURES ( cumulative_amount
          , 0 AS JAN
          , 0 AS FEB
          , 0 AS MAR
          , 0 AS APR
          , 0 AS MAY
          , 0 AS JUN
          , 0 AS JUL
          , 0 AS AUG
          , 0 AS SEP
          , 0 AS OCT
          , 0 AS NOV
          , 0 AS DEC
          )
          RULES (
          JAN[ANY] = SUM(cumulative_amount)['JAN']
          ,FEB[ANY] = SUM(cumulative_amount)['FEB']
          ,MAR[ANY] = SUM(cumulative_amount)['MAR']
          ,APR[ANY] = SUM(cumulative_amount)['APR']
          ,MAY[ANY] = SUM(cumulative_amount)['MAY']
          ,JUN[ANY] = SUM(cumulative_amount)['JUN']
          ,JUL[ANY] = SUM(cumulative_amount)['JUL']
          ,AUG[ANY] = SUM(cumulative_amount)['AUG']
          ,SEP[ANY] = SUM(cumulative_amount)['SEP']
          ,OCT[ANY] = SUM(cumulative_amount)['OCT']
          ,NOV[ANY] = SUM(cumulative_amount)['NOV']
          ,DEC[ANY] = SUM(cumulative_amount)['DEC']
          )
          ORDER BY period_year
          • 2. Re: Modeling months by years
            jeneesh
            As already mentioned, this does not look like a scenario to use MODEL

            You can use PIVOT
            WITH t AS (
            SELECT 2012 AS period_year, 1234 AS cost_center, 'JAN' AS period_month, 1 AS period_number, 100 AS month_amount, 100 AS cumulative_amount FROM dual UNION
            SELECT 2012 AS period_year, 1234 AS cost_center, 'FEB' AS period_month, 2 AS period_number, 200 AS month_amount, 300 AS cumulative_amount FROM dual UNION
            SELECT 2012 AS period_year, 1234 AS cost_center, 'MAR' AS period_month, 3 AS period_number, 300 AS month_amount, 600 AS cumulative_amount FROM dual UNION
            SELECT 2012 AS period_year, 1234 AS cost_center, 'APR' AS period_month, 4 AS period_number, 400 AS month_amount, 1000 AS cumulative_amount FROM dual UNION
            SELECT 2012 AS period_year, 1234 AS cost_center, 'MAY' AS period_month, 5 AS period_number, 500 AS month_amount, 1500 AS cumulative_amount FROM dual UNION
            SELECT 2012 AS period_year, 1234 AS cost_center, 'JUN' AS period_month, 6 AS period_number, 600 AS month_amount, 2100 AS cumulative_amount FROM dual UNION
            SELECT 2012 AS period_year, 1234 AS cost_center, 'JUL' AS period_month, 7 AS period_number, 700 AS month_amount, 2800 AS cumulative_amount FROM dual UNION
            SELECT 2012 AS period_year, 1234 AS cost_center, 'AUG' AS period_month, 8 AS period_number, 800 AS month_amount, 3600 AS cumulative_amount FROM dual UNION
            SELECT 2012 AS period_year, 1234 AS cost_center, 'SEP' AS period_month, 9 AS period_number, 900 AS month_amount, 4500 AS cumulative_amount FROM dual UNION
            SELECT 2012 AS period_year, 1234 AS cost_center, 'OCT' AS period_month, 10 AS period_number, 1000 AS month_amount, 5500 AS cumulative_amount FROM dual UNION
            SELECT 2012 AS period_year, 1234 AS cost_center, 'NOV' AS period_month, 11 AS period_number, 1100 AS month_amount, 6600 AS cumulative_amount FROM dual UNION
            SELECT 2012 AS period_year, 1234 AS cost_center, 'DEC' AS period_month, 12 AS period_number, 1200 AS month_amount, 7800 AS cumulative_amount FROM dual
            ),
            t2 as
            (select period_year,period_month,cumulative_amount
             from t
            )
            select * 
            from  t2
            pivot
             (
             sum(cumulative_amount) for period_month in ('JAN','FEB','MAR','APR','MAY',
                              'JUN','JUL','AUG','SEP','OCT','NOV','DEC'
                                              )
             )
            ;
            Edited by: jeneesh on Nov 6, 2012 9:52 AM
            • 3. Re: Modeling months by years
              ltps
              Thank you very much for taking the time to answer in such detail and for making such helpful suggestions. This will work!
              • 4. Re: Modeling months by years
                Rahul_India
                how can we solve this using decode?

                SELECT period_year ,
                decode(period_month,'JAN',cumulative_amount,0) as JAN,
                decode(period_month,'FEB',cumulative_amount,0) as FEB
                
                FROM t
                group by period_year