6 Replies Latest reply: Jan 30, 2013 2:59 PM by chris227 RSS

    SQL for Analytic Function

    942572
      Hello,

      I am using Oracle 11.2, need update some columns with analytic function. All I need is to update the total number for each batch(batchTotNbr) and last 12 month(yearTotNbr) for this item based on the batchStDt, batchEndDt and it's shipDt. Here are the details:

      create table tb_itemShipped(id number(5), shipDt number(6), shipNbr number(5), batchStDt number(6), batchEndDt number(6), batchTotNbr number(5), yearTotNbr number(5));

      insert into tb_itemShipped values(1, 20120501, 10, 20120301, 20120701, 0,0);
      insert into tb_itemShipped values(1, 20120601, 11, 20130301, 20130801, 0,0);
      insert into tb_itemShipped values(1, 20120901, 12, 20120802, 20120930, 0,0);
      insert into tb_itemShipped values(2, 20120501, 13, 20120301, 20120801, 0,0);

      The result I expect is:
      1, 20120501, 10, 20120301, 20120801, 10,10
      ..batchTot=10, yearTot=10
      1, 20120601, 11, 20130301, 20130801, 21,21
      ..batchTot=10+11=21, yearTot=10+11=21
      1, 20120901, 12, 20120802, 20120930, 12,33
      ..batchTot=12, yearTot=10+11+12=33
      2, 20120501, 13, 20120301, 20120801, 13,13
      ..batchTot=13, yearTot=13

      Edited by: 939569 on 30-Jan-2013 11:40 AM
        • 1. Re: SQL for Analytic Function
          riedelme
          939569 wrote:
          Hello,

          I am using Oracle 11.2, need update some columns with analytic function. All I need is to update the total number for each batch and each bundle(batchTotNbr, bundleTotNbr) for this item based on the batchStDt, batchEndDt, bundleStDt, bundleEndDt and it's shipDt. Here are the details:

          create table tb_itemShipped(id number(5), shipDt number(6), shipNbr number(5), batchStDt number(6), batchEndDt number(6), bundleStDt number(6), bundleEndDt number(6), batchTotNbr number(5), bundleTotNbr number(5));

          insert into tb_itemShipped values(1, 20120501, 10, 20120301, 20120801, 20120101, 20120901, 0,0);
          insert into tb_itemShipped values(1, 20120601, 11, 20130301, 20130801, 20120101, 20120901, 0,0);
          insert into tb_itemShipped values(1, 20120901, 12, 20120802, 20120930, 20120101, 20120901, 0,0);
          insert into tb_itemShipped values(2, 20120501, 13, 20120301, 20120801, 20120101, 20120901, 0,0);

          The result I expect is:
          1, 20120501, 10, 20120301, 20120801, 20120101, 20120901, 10,10
          1, 20120601, 11, 20130301, 20130801, 20120101, 20120901, 21,21
          1, 20120901, 12, 20120802, 20120930, 20120101, 20120901, 12,33
          2, 20120501, 13, 20120301, 20120801, 20120101, 20120901, 13,13
          Good job posting the data and results but some of the results are not clear.

          Where do the expected bundletotnbr vlaues come from? Where in the data are the 10, 21, 33, and 13 derived?

          Although if done very carefully the number-dates can work it is a better idea to store dates as actual Oracle date types.
          • 2. Re: SQL for Analytic Function
            Frank Kulash
            Hi,
            939569 wrote:
            Hello,

            I am using Oracle 11.2, need update some columns with analytic function. All I need is to update the total number for each batch(batchTotNbr) and last 12 month(yearTotNbr) for this item based on the batchStDt, batchEndDt and it's shipDt. Here are the details:

            create table tb_itemShipped(id number(5), shipDt number(6), shipNbr number(5), batchStDt number(6), batchEndDt number(6), batchTotNbr number(5), yearTotNbr number(5));
            Dates should be stored in DATE columns, not NUMBER columns.
            Given that they are NUMBER columns, they need to store at least 8 digtis.
            insert into tb_itemShipped values(1, 20120501, 10, 20120301, 20120701, 0,0);
            insert into tb_itemShipped values(1, 20120601, 11, 20130301, 20130801, 0,0);
            insert into tb_itemShipped values(1, 20120901, 12, 20120802, 20120930, 0,0);
            insert into tb_itemShipped values(2, 20120501, 13, 20120301, 20120801, 0,0);
            The result I expect is:
            1, 20120501, 10, 20120301, 20120801, 10,10
            ..batchTot=10, yearTot=10
            1, 20120601, 11, 20130301, 20130801, 21,21
            ..batchTot=10+11=21, yearTot=10+11=21
            1, 20120901, 12, 20120802, 20120930, 12,33
            ..batchTot=12, yearTot=10+11+12=33
            2, 20120501, 13, 20120301, 20120801, 13,13
            ..batchTot=13, yearTot=13
            Sorry, I don't see how you're getting batchTot.
            You can set yearTotNbr like this:
            MERGE INTO  tb_itemShipped              dst
            USING  (
                       WITH    got_dates     AS
                    (
                      SELECT  id
                      ,     shipDt
                      ,     TO_DATE (shipDt, 'YYYYMMDD')     AS shipDate
                      ,     shipNbr
                      FROM     tb_itemShipped
            --          WHERE     ...     -- if you need and filtering, put it here
                    )     
                       SELECT  id
                    ,        shipDt
                    ,        SUM (shipNbr) OVER ( PARTITION BY  id
                                                    ORDER BY      shipDate
                                        RANGE BETWEEN 365 PRECEDING
                                           AND     CURRENT ROW
                                      ) AS yearTotNbr
                    FROM    got_dates
                   )                    src
            ON     (   dst.id     = src.id
                   AND dst.shipDt     = src.shipDt
                   )
            WHEN MATCHED THEN UPDATE
            SET    dst.yearTotNbr     = src.yearTotNbr
            ;
            
            SELECT     *
            FROM     tb_itemshipped
            ORDER BY     ID, SHIPDT
            ;
            Whatever batchTotNbr is, you can probably modify the statement above to get it.
            • 3. Re: SQL for Analytic Function
              chris227
              Hi,

              your create script is faulty because number(5) is to short for your date-numbers.
              If you have influence on the table design i would strongly recommend to use date datatype instead.
              For example you are able to calculate the last 12 month more accuratly. (I could have done this by to_date also, but i slipped this, just keep the eye on the analytics).

              Perhaps you are looking for something like the below. Didnt find out yet how to derive the column batchtotbnr from your data
              select
               id
              ,shipdt
              ,shipnbr
              ,batchstdt
              ,batchenddt
              ,sum(shipnbr) over (partition by id order by shipdt
               range between (BATCHSTDT - 1200) preceding and current row)  batchtotnbr
              ,sum(shipnbr) over (partition by id order by shipdt
               range between (SHIPDT - 1200) preceding and current row)  yeartotnbr
              from tb_itemShipped
              
              ID     SHIPDT     SHIPNBR     BATCHSTDT     BATCHENDDT     BATCHTOTNBR     YEARTOTNBR
              1     20120501     10     20120301     20120701     10     10
              1     20120601     11     20130301     20130801     21     21
              1     20120901     12     20120802     20120930     33     33
              2     20120501     13     20120301     20120801     13     13
              • 4. Re: SQL for Analytic Function
                942572
                Thanks for all the help, to simplize the problem, the following is the new schema:

                create table tb_item(itemId number(5), itemSerNbr number(6), itemWeight number(6), batchStNbr number(6), batchEndNbr number(6), totWeightBatch number(6));

                insert into tb_item values(1, 12, 1, 1, 100, 0);
                insert into tb_item values(1, 58, 10, 1, 100, 0);
                insert into tb_item values(1, 63, 20, 50, 70, 0);
                insert into tb_item values(1, 101,30, 60, 120, 0);

                The total weight of each batch is sum of itemWeight whose itemSerNbr is between batchStNbr and batchEndNbr. Thus the result shall be as followings:
                1, 12, 1, 1, 100, 31
                ...1+10+20=31 for itemSeq between 1 and 100
                1, 58, 10, 1, 100, 31
                ...1+10+20=31 for itemSeq between 1 and 100
                1, 63, 20, 50, 70, 30
                ...10+20=30 for itemSeq between 50 and 70
                1, 101, 30, 60, 120, 60
                ...20+30=50 for itemSeq between 60 and 120

                Thanks!
                • 5. Re: SQL for Analytic Function
                  Frank Kulash
                  Hi
                  939569 wrote:
                  Thanks for all the help, to simplize the problem, the following is the new schema:

                  create table tb_item(itemId number(5), itemSerNbr number(6), itemWeight number(6), batchStNbr number(6), batchEndNbr number(6), totWeightBatch number(6));

                  insert into tb_item values(1, 12, 1, 1, 100, 0);
                  insert into tb_item values(1, 58, 10, 1, 100, 0);
                  insert into tb_item values(1, 63, 20, 50, 70, 0);
                  insert into tb_item values(1, 101,30, 60, 120, 0);

                  The total weight of each batch is sum of itemWeight whose itemSerNbr is between batchStNbr and batchEndNbr. Thus the result shall be as followings:
                  1, 12, 1, 1, 100, 31
                  ...1+10+20=31 for itemSeq between 1 and 100
                  What is itemSeq? Did you mena itemSerNbr
                  1, 58, 10, 1, 100, 31
                  ...1+10+20=31 for itemSeq between 1 and 100
                  1, 63, 20, 50, 70, 30
                  ...10+20=30 for itemSeq between 50 and 70
                  1, 101, 30, 60, 120, 60
                  Did you mean 50 in the last column above?
                  ...20+30=50 for itemSeq between 60 and 120

                  Thanks!
                  UPDATE  tb_item         m
                  SET     totWeightBatch = (
                                      SELECT  SUM (itemWeight)
                                      FROM    tb_item
                                      WHERE   itemSerNbr  BETWEEN  m.batchStNbr
                                                           AND       m.batchEndNbr
                                      AND     itemId      = m.itemId     -- Maybe
                                         )
                  ;
                  • 6. Re: SQL for Analytic Function
                    chris227
                    select
                     itemId
                    ,itemSerNbr
                    ,itemWeight
                    ,batchStNbr
                    ,batchEndNbr
                    ,(select sum(ITEMWEIGHT) from tb_item
                      where
                      ITEMSERNBR between t.BATCHSTNBR and t.BATCHENDNBR)
                     totWeightBatch
                    from tb_item t
                    
                    ITEMID     ITEMSERNBR     ITEMWEIGHT     BATCHSTNBR     BATCHENDNBR     TOTWEIGHTBATCH
                    1     12     1     1     100     31
                    1     63     20     50     70     30
                    1     58     10     1     100     31
                    1     101     30     60     120     50