Sum the quantity in Procedure RSS

• • Reply
  

  ying87

  Member

  37 Points

  62 Posts

  Sum the quantity in Procedure

  Nov 06, 2012 02:21 PM|LINK

  Hi,

  I have a procedure that used to sum the quantity, but i have no idea on how to do the sum in the procedure.

  In the procedure, first it will run the sql query to get one week data. Second, sum the quantity from Day 1 until Day 7. Third, it will store the a temporary table instend of a real table (because i need to use the data from temporary to do checking before insert into real table).

  Below here will be the data from sql query, example from 11/04 until 11/10 data:

  Category Line Quantity DateIn
  A1 LLC 78 11/04/2012
  A1 RTC 49 11/04/2012
  A2 BAA 98 11/04/2012
  A2 KIO 55 11/04/2012
  A1 LLC 78 11/05/2012
  A1 RTC 49 11/05/2012
  A2 BAA 98 11/05/2012
  A2 KIO 55 11/05/2012
  ....
  A1 LLC 66 11/10/2012
  A1 RTC 23 11/10/2012
  A2 BAA 45 11/10/2012
  A2 KIO 90 11/10/2012

  After the quantity sum up, the result only will take the lastest date with total quantity of Day 1 until Day 7 and insert into temporary table as below:

  Category	Line	Quantity	DateIn	
  A1		LLC	1056		11/10/2012
  A1		RTC	2344		11/10/2012
  A2		BAA	1427		11/10/2012
  A2		KIO	1004		11/10/2012

  Any idea / guide to do the sum...? Please kindly advice.
  
  

   

• • Reply
  

  Raigad

  Contributor

  5131 Points

  957 Posts

  Re: Sum the quantity in Procedure

  Nov 06, 2012 02:36 PM|LINK

  Try this one

  SELECT emp.Quantity ,emp.Category, mgr.Line
  FROM yourTableName emp, yourTableName mgr
  WHERE emp.Line = mgr.Line
  
  you can use this to fetch data from your temporary table or add where conditions in same query and change the table name 
  to your real table name.

  Babasaheb Kale
  Mark as Answer, if the post helped you...
  Visit My Blog
  
  • Marked as answer by Young Yang - MSFT on Nov 30, 2012 07:33 AM
• • Reply
  

  Lannie

  Contributor

  3840 Points

  749 Posts

  Re: Sum the quantity in Procedure

  Nov 07, 2012 12:47 AM|LINK

  Using your visible data

  Using Oracle Analytics

  Inner SQL gets the last date in the partition of category and line

  Outer SQL does the rollup math SUMs

  CREATE TABLE TEST_SUM
  (
    CATEGORY  VARCHAR2(8 BYTE),
    LINE      VARCHAR2(8 BYTE),
    QUANTITY  NUMBER(8),
    DATEIN    DATE
  );
  
  SET DEFINE OFF;
  Insert into TEST_SUM Values
     ('A1', 'LLC', 78, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  Insert into TEST_SUM Values
     ('A1', 'RTC', 49, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  Insert into TEST_SUM Values
     ('A2', 'BAA', 98, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  Insert into TEST_SUM Values
     ('A2', 'KIO', 55, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  Insert into TEST_SUM Values
     ('A1', 'LLC', 78, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  Insert into TEST_SUM Values
     ('A1', 'RTC', 49, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  Insert into TEST_SUM Values
     ('A2', 'BAA', 98, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  Insert into TEST_SUM Values
     ('A2', 'KIO', 55, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  Insert into TEST_SUM Values
     ('A1', 'LLC', 66, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  Insert into TEST_SUM Values
     ('A1', 'RTC', 23, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  Insert into TEST_SUM Values
     ('A2', 'BAA', 45, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  Insert into TEST_SUM Values
     ('A2', 'KIO', 90, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
  COMMIT;
  
  SELECT
    CATEGORY,
    LINE,
    QUANTITY,
    DATEIN,
    LAST_VALUE(DATEIN)
       OVER
      (PARTITION BY CATEGORY, LINE
       ORDER BY CATEGORY, LINE, DATEIN
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DATELAST
  FROM
    TEST_SUM
  /
  
  CATEGORY LINE       QUANTITY DATEIN    DATELAST
  -------- -------- ---------- --------- ---------
  A1       LLC              78 04-NOV-12 10-NOV-12
  A1       LLC              78 05-NOV-12 10-NOV-12
  A1       LLC              66 10-NOV-12 10-NOV-12
  A1       RTC              49 04-NOV-12 10-NOV-12
  A1       RTC              49 05-NOV-12 10-NOV-12
  A1       RTC              23 10-NOV-12 10-NOV-12
  A2       BAA              98 04-NOV-12 10-NOV-12
  A2       BAA              98 05-NOV-12 10-NOV-12
  A2       BAA              45 10-NOV-12 10-NOV-12
  A2       KIO              55 04-NOV-12 10-NOV-12
  A2       KIO              55 05-NOV-12 10-NOV-12
  A2       KIO              90 10-NOV-12 10-NOV-12
  
  12 rows selected.
  
  well that windowing analytic function got the last available date within category and line partition
  
  SELECT DISTINCT
    a.CATEGORY,
    a.LINE,
    SUM(a.QUANTITY) OVER (PARTITION BY a.CATEGORY, a.LINE) AS TOTAL_QUANTITY,
    TO_CHAR(a.DATELAST,'MM/DD/YYYY') AS DATELAST
  FROM
    (
    SELECT
      CATEGORY,
      LINE,
      QUANTITY,
      DATEIN,
      LAST_VALUE(DATEIN)
         OVER
        (PARTITION BY CATEGORY, LINE
         ORDER BY CATEGORY, LINE, DATEIN
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DATELAST
    FROM
      TEST_SUM
    ) a
  ORDER BY a.CATEGORY, a.LINE
  /
  
  CATEGORY LINE     TOTAL_QUANTITY DATELAST
  -------- -------- -------------- ----------
  A1       LLC                 222 11/10/2012
  A1       RTC                 121 11/10/2012
  A2       BAA                 241 11/10/2012
  A2       KIO                 200 11/10/2012
  
  and that completes the mathematics for the final desired result table.
  
  Oracle is fun.

  • Edited by Lannie on Nov 07, 2012 12:48 AM
  • Marked as answer by Young Yang - MSFT on Nov 30, 2012 07:33 AM