Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2015
    Posts
    1

    Question Unanswered: Sumarize two rows values based on 2 most recent dates

    Hello I have a table similar to the next:

    CODE | AMOUNT1 | AMOUNT2 | LOAD_DATE |
    2013-XX-ZZ | 500 | 200 | 2015/04/04 |
    2013-XX-ZZ | 300 | 200 | 2015/04/04 |
    2013-XX-ZZ | 600 | 100 | 2015/03/01 |
    2013-XX-ZZ | 200 | 150 | 2015/02/03 |
    2013-XX-ZZ | 400 | 150 | 2015/04/03 |
    2014-RR-YY | 900 | 200 | 2015/04/04 |
    2014-RR-YY | 500 | 100 | 2015/03/02 |
    2014-RR-YY | 100 | 50 | 2015/03/02 |
    2014-RR-YY | 100 | 300 | 2015/01/02 |

    I need to make a query so I can get the total sum from the fields (MONTO1, MONTO2) just using the records with most recent date, grouping the results by the CLAVE field, then get the total sum from the same fields (MONTO1, MONTO2) but this time using the records with the inmediate prior date, I mean the date prior to the most recent date I mean the total from the last date and the total from the prior date.

    Finally I need to substract the records of the prior date result from the most records of the most recent date.

    Using the data of the example table I put before I need this:

    SUM TOTAL OF RECORDS WITH MOST RECENT DATE:
    CODE | AMOUNT1 | AMOUNT2 | LOAD_DATE |
    2013-XX-ZZ | 800 | 400 | 2015/04/04 |

    SUM TOTAL OF RECORDS WITH PRIOR LOAD_DATE:
    CODE | AMOUNT1 | AMOUNT2 | LOAD_DATE |
    2013-XX-ZZ | 400 | 150 | 2015/04/03 |

    THE RESULT I NEED TO GET IS:
    CODE | AMOUNT1 | AMOUNT2 |
    2013-XX-ZZ | 400 | 250 |

    I have only have the first part of the query to get the most recent LOAD_DATE, so I need your help to know how to solve the rest of the problem, I really don't know how to do it, I'm a begginer with SQL Queries.


    SELECT CODE, SUM(AMOUNT1), SUM(AMOUNT2), MAX(LOAD_DATE) FROM TABLE1 GROUP BY CODE.

    Thanks in Advanced

    Vic
    Last edited by vicsense; 04-20-15 at 12:22.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You aren't consistent while describing the problem.
    • What are MONTO1 and MONTO2? I suppose these are AMOUNTS.
    • What is CLAVE? I suppose it is CODE.
    • Where did "350" (as AMOUNT1) come from in this line:
      Code:
      SUM TOTAL OF RECORDS WITH PRIOR LOAD_DATE:
      
      CODE       | AMOUNT1 | AMOUNT2 | LOAD_DATE  |
      2013-XX-ZZ | 350     | 150     | 2015/04/03 |
      Regarding sample data you provided, it should be 400, not 350.


    Anyway, here's one option: select last date values, select previous date values, subtract them. I assume that your date format is YYYY/MM/DD. It is different from default date format here, where I live (we use DD.MM.YYYY) so I wasn't sure what "yyyy/04/03" is: third of April or fourth of March. Next time, make sure we know it for sure.

    Sample data:
    Code:
    SQL> alter session set nls_date_format = 'yyyy/mm/dd';
    
    Session altered.
    
    SQL> select * from test
      2  order by code, load_date desc;
    
    CODE                     AMOUNT LOAD_DATE
    -------------------- ---------- ----------
    2013-XX-ZZ                  500 2015/04/04
    2013-XX-ZZ                  300 2015/04/04
    2013-XX-ZZ                  400 2015/04/03
    2013-XX-ZZ                  600 2015/03/01
    2013-XX-ZZ                  200 2015/02/03
    2014-RR-YY                  900 2015/04/04
    2014-RR-YY                  500 2015/03/02
    2014-RR-YY                  100 2015/03/02
    2014-RR-YY                  100 2015/01/02
    
    9 rows selected.
    The result:
    Code:
    SQL> with last_date_values
      2       as (  select t.code, sum (t.amount) sum_amount, t.load_date
      3               from test t
      4              where t.load_date = (select max (t1.load_date)
      5                                     from test t1
      6                                    where t1.code = t.code)
      7           group by t.code, t.load_date),
      8       previous_date_values
      9       as (  select t.code, sum (t.amount) sum_amount, t.load_date
     10               from test t, last_date_values ld
     11              where     ld.code = t.code
     12                    and t.load_date =
     13                           (select max (t1.load_date)
     14                              from test t1
     15                             where     t1.code = t.code
     16                                   and t1.load_date < ld.load_date)
     17           group by t.code, t.load_date)
     18    select ld.code, ld.sum_amount - pd.sum_amount amount
     19      from last_date_values ld, previous_date_values pd
     20     where pd.code = ld.code
     21  order by ld.code;
    
    CODE                     AMOUNT
    -------------------- ----------
    2013-XX-ZZ                  400
    2014-RR-YY                  300
    
    SQL>

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    How about this?:
    Code:
    SQL> WITH Table1 ( Code, Amount1, Amount2, Load_Date )
      2      AS (SELECT '2013-XX-ZZ', 500, 200, TO_DATE ( '2015/04/04 ', 'yyyy/mm/dd' ) FROM DUAL UNION ALL
      3          SELECT '2013-XX-ZZ', 300, 200, TO_DATE ( '2015/04/04 ', 'yyyy/mm/dd' ) FROM DUAL UNION ALL
      4          SELECT '2013-XX-ZZ', 600, 100, TO_DATE ( '2015/03/01 ', 'yyyy/mm/dd' ) FROM DUAL UNION ALL
      5          SELECT '2013-XX-ZZ', 200, 150, TO_DATE ( '2015/02/03 ', 'yyyy/mm/dd' ) FROM DUAL UNION ALL
      6          SELECT '2013-XX-ZZ', 400, 150, TO_DATE ( '2015/04/03 ', 'yyyy/mm/dd' ) FROM DUAL UNION ALL
      7          SELECT '2014-RR-YY', 900, 200, TO_DATE ( '2015/04/04 ', 'yyyy/mm/dd' ) FROM DUAL UNION ALL
      8          SELECT '2014-RR-YY', 500, 100, TO_DATE ( '2015/03/02 ', 'yyyy/mm/dd' ) FROM DUAL UNION ALL
      9          SELECT '2014-RR-YY', 100,  50, TO_DATE ( '2015/03/02 ', 'yyyy/mm/dd' ) FROM DUAL UNION ALL
     10          SELECT '2014-RR-YY', 100, 300, TO_DATE ( '2015/01/02 ', 'yyyy/mm/dd' ) FROM DUAL)
     11  -- -----------------------------------
     12  SELECT * FROM (
     13  SELECT Code, Load_Date, Amount1, Amount2
     14       , ( Amount1 - LEAD(Amount1,1) OVER (ORDER BY code, load_date desc)) AS recent_amt1
     15       , ( Amount2 - LEAD(Amount2,1) OVER (ORDER BY code, load_date desc)) AS recent_amt2
     16       , DENSE_RANK ( ) OVER ( PARTITION BY Code ORDER BY Load_Date DESC ) AS Rnk
     17    FROM (
     18    SELECT Code
     19         , Load_Date
     20         , SUM ( Amount1 ) Amount1
     21         , SUM ( Amount2 ) Amount2
     22      FROM Table1
     23  GROUP BY Code, Load_Date))
     24  WHERE RNK = 1
     25* ORDER BY Code, Load_Date desc
    SQL> /
    
    CODE        LOAD_DATE       AMOUNT1    AMOUNT2 RECENT_AMT1 RECENT_AMT2        RNK
    ----------- ------------ ---------- ---------- ----------- ----------- ----------
    2013-XX-ZZ  2015/04/04          800        400         400         250          1
    2014-RR-YY  2015/04/04          900        200         300          50          1
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •