8 Replies Latest reply: Jun 24, 2014 5:45 AM by Karthick_Arp RSS

    SQL to find cumulative value

    Sami

      Hi All,

       

      I have table called XXXXX with following records

       

      PERIOD

      SUMMARY_DTYEARSUMMARY_VALUESUMMARY_DATA_TY_ID
      1131-05-2014201310021
      1230-06-20142013
      131-07-20142014
      231-08-20142014
      330-09-20142014
      431-10-20142014

       

      I need SQL  to generate result such way that each row will  result column.

       

       

      PERIODSUMMARY_DTYEARSUMMARY_VALUESUMMARY_DATA_TY_IDResult
      1131-05-2014201310021       100*53
      1230-06-20142013100*53*53
      131-07-20142014100*53*53*53
      231-08-20142014100*53*53*53*53
      330-09-20142014100*53*53*53*53*53
      431-10-20142014100*53*53*53*53*53*53

       

      Kindly guide me to archive above result.

       

      Thanks & Regards

      Sami

        • 1. Re: SQL to find cumulative value
          brunovroman

          Hello Sami,

           

          (why 53?)

          Not sure I understand, but what about:

           

          WITH rows_in_order AS

          ( SELECT period, summary_dt, year, summary_value, summary_data_ty_id

              FROM xxxxx

              ORDER BY summary_dt

          )

          SELECT r.*, 100 * POWER( 53, ROWNUM ) result

            FROM rows_in_order r

          ;

           

          This is probably not what you want, but please give more details of what is needed.

           

          Best regards,

           

          Bruno Vroman.

          • 2. Re: SQL to find cumulative value
            Frank Kulash

            Hi,

             

            Here's a variation of Bruno's solution, that takes 100 from the table, and avoids the sub--query:

             

            SELECT    period, summary_dt, year, summary_value, summary_data_ty_id

            ,         MIN (summary_value) OVER ()

                    * POWER ( 53

                            , ROW_NUMBER () OVER (ORDER BY summary_dt)

                            )    AS result

            FROM      xxxxx

            ORDER BY  summary_dt

            ;

             

            I hope this answers your question.

            If not, post  CREATE TABLE and INSERT statements for your sample data (relevant columns only), and the exact results you want from that data.

            Point out where the query above is producing the wrong results, and explain, using specific examples, how you get the right results from the given data in those places.

            Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

            See the forum FAQ: https://community.oracle.com/message/9362002#9362002

            • 3. Re: SQL to find cumulative value
              Sami

              Hi Bruno Vroman,

               

              Thanks for your reply..

              brunovroman wrote:

               

              Hello Sami,

               

              (why 53?)

              Not sure I understand, but what about:

               

              WITH rows_in_order AS

              ( SELECT period, summary_dt, year, summary_value, summary_data_ty_id

                  FROM xxxxx

                  ORDER BY summary_dt

              )

              SELECT r.*, 100 * POWER( 53, ROWNUM ) result

                FROM rows_in_order r

              ;

               

              This is probably not what you want, but please give more details of what is needed.

               

              Best regards,

               

              Bruno Vroman.

              53 is nothing but percentage of growth by year.it % predication for up coming month..

               

              As per SQL it display the following result..

              
              
              SELECT PERIOD,
              SUMMARY_DT,
              YEAR,
              SUMMARY_VALUE,
              SUMMARY_DATA_TY_ID, (summary_value * POWER( 53, ROWNUM )) result
                FROM BI_ANALYSIS
                ORDER BY summary_dt
              ;
              
              
              

               

               

              PERIOD

              SUMMARY_DTYEARSUMMARY_VALUESUMMARY_DATA_TY_IDResult
              1131-05-2014201313232753832.48701335953121.44
              1230-06-2014201326465507608.9674341610873568.64
              131-07-2014201439698261385.445910158060280150.88
              231-08-2014201452931015161.92417651169445841683.52
              330-09-2014201466163768938.427669389969932274631.2
              431-10-2014201479396522714.881759773201839451821899.52

               

              as per my previous post first record of table SUMMARY_VALUE column will have 100 then it should be multiples by 53..

              so first row value will be 5300.. on second row calculation should be 5300*53*53=14887700

              3rd row calculation should be value of previous row (14887700)*53*53*53=789048100.. so on up to last record of table.

               

              above SQL by brunovroman replaces the original value of summary_value.. it should not changed.

               

              Thanks & Regards

              Sami

              • 4. Re: SQL to find cumulative value
                brunovroman

                Hello Sami,

                 

                remark about 53: <<53 is nothing but percentage of growth by year.it % predication for up coming month..>>

                 

                If this is a percentage of growth, maybe you don't want 100 * 53 * 53 * 53... but rather 1.53 * 1.53 * 1.53

                Indeed, if n is a percentage of growth applied to X, after one period X becomes X * ( 1 + n/100 ), and one period later: X * (1 + n/100)^2 and so on.

                So in your case:


                  summary_value * POWER( 1.53, ROWNUM )

                 

                (instead of  " 100 * POWER( 53, ROWNUM )")

                 

                Best regards,

                 

                Bruno

                • 5. Re: SQL to find cumulative value
                  Karthick_Arp
                  Using MODEL clause...
                  
                  
                  SQL> select * from xxxxx;
                  
                      PERIOD SUMMARY_DT               YEAR_ SUMMARY_VALUE SUMMARY_DATA_TY_ID
                  ---------- ------------------- ---------- ------------- ------------------
                          11 05-31-2014 00:00:00       2013           100                 21
                          12 06-30-2014 00:00:00       2013
                           1 07-31-2014 00:00:00       2014
                           2 08-31-2014 00:00:00       2014
                           3 09-30-2014 00:00:00       2014
                           4 10-31-2014 00:00:00       2014
                  
                  6 rows selected.
                  
                  SQL> select *
                    2    from xxxxx
                    3   model
                    4   dimension by
                    5   (
                    6      row_number() over(order by summary_dt) rno
                    7   )
                    8   measures
                    9   (
                   10      summary_dt
                   11    , period
                   12    , year_
                   13    , summary_value
                   14    , summary_data_ty_id
                   15    , 0 result
                   16   )
                   17   rules
                   18   (
                   19      result [any] = nvl(result [cv()-1], summary_value[cv()]) * 53
                   20   );
                  
                         RNO SUMMARY_DT              PERIOD      YEAR_ SUMMARY_VALUE SUMMARY_DATA_TY_ID           RESULT
                  ---------- ------------------- ---------- ---------- ------------- ------------------ ----------------
                           1 05-31-2014 00:00:00         11       2013           100                 21             5300
                           2 06-30-2014 00:00:00         12       2013                                            280900
                           3 07-31-2014 00:00:00          1       2014                                          14887700
                           4 08-31-2014 00:00:00          2       2014                                         789048100
                           5 09-30-2014 00:00:00          3       2014                                       41819549300
                           6 10-31-2014 00:00:00          4       2014                                     2216436112900
                  
                  6 rows selected.
                  
                  SQL>
                  • 6. Re: SQL to find cumulative value
                    chris227

                    select

                    period,

                    summary_dt,

                    year,

                    summary_value,

                    summary_data_ty_id,

                    last_value (summary_value) ignore nulls over (order by summary_date)

                    * power(53, count(*) over (order by summary_date))

                      result

                    from xxxxx

                    • 7. Re: SQL to find cumulative value
                      Sami

                      Hi All,

                       

                      Thanks for all your reply..

                       

                      @Karthick_Arp, your SQL result also matching my requirement. But on First come first basis I got answer from Frank, So i have mark his commands as answered first.

                       

                      Thanks & Regards

                      Sami

                      • 8. Re: SQL to find cumulative value
                        Karthick_Arp

                        I provided MODEL solution just to show you a alternative technique. A analytical solution is sufficient (simple as well) in this case as shown by Frank and others.