# Thread: Sumarize two rows values based on 2 most recent dates

1. Registered User
Join Date
Apr 2015
Posts
1

## 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.

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

2. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,110
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;

-------------------- ---------- ----------
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
5                                     from test t1
6                                    where t1.code = t.code)
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
14                              from test t1
15                             where     t1.code = t.code
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. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713
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
20         , SUM ( Amount1 ) Amount1
21         , SUM ( Amount2 ) Amount2
22      FROM Table1
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```

#### Posting Permissions

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