Home » SQL & PL/SQL » SQL & PL/SQL » difference amount between 2 dates
difference amount between 2 dates [message #674773] Fri, 15 February 2019 12:53 Go to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Hi Team,

could you please help me to get the output in the below desired format based on the given table data.


CREATE TABLE my_table (
    account int (6),
    pay_amount int(5),
    pay_date date
);

insert into my_table values (258741, 25, '01-01-2019');
insert into my_table values (258741, 45, '15-01-2019');
insert into my_table values (258742, 85, '01-01-2019');
insert into my_table values (258742, 15, '15-01-2019');
insert into my_table values (258743, 55, '01-01-2019');
insert into my_table values (258743, 35, '15-01-2019');
Note:
pay_date_old = 01-01-2019
pay_date_new = 15-01-2019

difference = pay_date_old amount - pay_date_new amount
ex: for account 258741, difference = 25-45 = -20
%diference = difference/pay_date_old amount
ex: %difference = (-20/25)*100 = 80


output:
account        pay_date_old      pay_date_new    difference    %diference
258741           01-01-2019       15-01-2019        -20          80%
258742           01-01-2019       15-01-2019         70          82.35%
258743           01-01-2019       15-01-2019         20          36.36%

Thank you.

Regards
Suji
Re: difference amount between 2 dates [message #674774 is a reply to message #674773] Fri, 15 February 2019 13:46 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2987
Registered: January 2010
Location: Connecticut, USA
Senior Member
Assuming there are always only two dates per account:

with t as (
           select  account,
                   min(pay_date) pay_date_old,
                   max(pay_date) pay_date_new,
                   min(pay_amount) keep(dense_rank first order by pay_date) pay_amount_old,
                   max(pay_amount) keep(dense_rank last order by pay_date) pay_amount_new
             from  my_table
             group by account
          )
select  account,
        pay_amount_old,
        pay_amount_new,
        pay_amount_old - pay_amount_new difference,
        abs(pay_amount_old - pay_amount_new) * 100 / pay_amount_old percent_difference
  from  t
  order by account
/

   ACCOUNT PAY_AMOUNT_OLD PAY_AMOUNT_NEW DIFFERENCE PERCENT_DIFFERENCE
---------- -------------- -------------- ---------- ------------------
    258741             25             45        -20                 80
    258742             85             15         70         82.3529412
    258743             55             35         20         36.3636364

SQL> 

And if you are on 12C:

select  account,
        pay_amount_old,
        pay_amount_new,
        pay_amount_old - pay_amount_new difference,
        abs(pay_amount_old - pay_amount_new) * 100 / pay_amount_old percent_difference
  from  my_table
  match_recognize(
                  partition by account
                  order by pay_date
                  measures first(pay_date) pay_date_old,
                           last(pay_date) pay_date_new,
                           first(pay_amount) pay_amount_old,
                           last(pay_amount) pay_amount_new
                  pattern(up+)
                  define up as 1 = 1
                 )
/

   ACCOUNT PAY_AMOUNT_OLD PAY_AMOUNT_NEW DIFFERENCE PERCENT_DIFFERENCE
---------- -------------- -------------- ---------- ------------------
    258741             25             45        -20                 80
    258742             85             15         70         82.3529412
    258743             55             35         20         36.3636364

SQL> 

SY.
Re: difference amount between 2 dates [message #674775 is a reply to message #674773] Fri, 15 February 2019 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please take care to post a WORKING test case:
SQL> CREATE TABLE my_table (
  2      account int (6),
  3      pay_amount int(5),
  4      pay_date date
  5  );
    account int (6),
                *
ERROR at line 2:
ORA-00907: missing right parenthesis

'15-01-2019' is NOT a date it is a string when you do that you ask for an implicite conversion which will not work for most of the world:
SQL> insert into my_table values (258741, 45, '15-01-2019');
insert into my_table values (258741, 45, '15-01-2019')
                                         *
ERROR at line 1:
ORA-01843: not a valid month

Quote:
ex: %difference = (-20/25)*100 = 80

For me this is not correct.

SQL> with
  2    data as (
  3      select account, pay_amount,
  4             lag(pay_date) over (partition by account order by pay_date) pay_date_old,
  5             pay_date pay_date_new,
  6             lag(pay_amount) over (partition by account order by pay_date) pay_amount_old
  7      from my_table
  8      )
  9  select account, pay_date_old, pay_date_new, pay_amount - pay_amount_old difference,
 10         to_char(100 * (pay_amount-pay_amount_old) / pay_amount_old, 'S990.00') || '%'
 11         "%DIFFERENCE"
 12  from data
 13  where pay_date_old is not null
 14  order by account, pay_date_new
 15  /
   ACCOUNT PAY_DATE_OL PAY_DATE_NE DIFFERENCE %DIFFERE
---------- ----------- ----------- ---------- --------
    258741 01-JAN-2019 15-JAN-2019         20  +80.00%
    258742 01-JAN-2019 15-JAN-2019        -70  -82.35%
    258743 01-JAN-2019 15-JAN-2019        -20  -36.36%

3 rows selected.
Re: difference amount between 2 dates [message #674780 is a reply to message #674774] Sat, 16 February 2019 23:59 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Thank you for the reply Solomon.
As you mentioned that two dates per account, yes we will query always between two dates.
Re: difference amount between 2 dates [message #674781 is a reply to message #674775] Sun, 17 February 2019 00:02 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Thank you Michal for the sql logic.
I will take care of syntax and other stuff while posting test cases.
Re: difference amount between 2 dates [message #674799 is a reply to message #674781] Mon, 18 February 2019 02:54 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Hi Michel/Solomon,

continuing with my previous requirement, there is some change in requirement. Please see the revised table data and expected output for reference. Please help me with modified sql. Thank you very much.

drop the existing table.

drop table my_table;

Create Table my_table.
  
CREATE TABLE my_table (key_number DECIMAL(10) NOT NULL,
   pay_date DATE,
   account VARCHAR2(10) NOT NULL,
   pay_amount DECIMAL(26, 3) NOT NULL);

insert rows into my_table for pay_date = 10-JAN-19. total row count is 11.

     
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102547', '10-JAN-19', '721017', 150.25);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102547', '10-JAN-19', '854793', 140.50);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102547', '10-JAN-19', '854793', -25.42);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102547', '10-JAN-19', '854793', 50.25);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102547', '10-JAN-19', '854895', 51.52);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102547', '10-JAN-19', '854897', 72.85);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102548', '10-JAN-19', '854898', 51.52);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102548', '10-JAN-19', '854899', 72.85);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102547', '10-JAN-19', '854900', -19.32);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102547', '10-JAN-19', '854905', 100.4);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102547', '10-JAN-19', '854920', 210.0);

insert rows into my_table for pay_date = 20-JAN-19. total row count is 6.

  
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102548', '20-JAN-19', '721017', 100.25);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102548', '20-JAN-19', '854793', 140.50);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102548', '20-JAN-19', '854795', 51.52);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102548', '20-JAN-19', '854797', 50.25);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102548', '20-JAN-19', '854900', -19.32);
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102548', '20-JAN-19', '854905', 90.4);

output should be as below:

Account	 previous_amount(10-JAN-19)	current_amount(20-JAN-19)	differnec  	 %difference
721017	  150.25	                  100.25	                 -50	          -33.27
854793	  165.33	                  140.5	                         -24.83    	  -15.01
854895	  51.52	                          51.52	                         0	           0
854897	  72.85	                          50.25	                         -22.6	          -31.02
854898	  51.52		                                                 -51.52	          -100
854899	  72.85		                                                 -72.85	          -100
854900	 -19.32	                         -19.32	                         0	           0
854905	  100.4    	                  90.4	                         -10	           -9.96
854920	  210		                                                 -210	           -100

Regards
Suji
Re: difference amount between 2 dates [message #674801 is a reply to message #674799] Mon, 18 February 2019 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
there is some change in requirement

Please explain with words the changes and what is the purpose of KEY_NUMBER.
Also explain your result; how 11 + 6 rows result to 9 rows?

'10-JAN-19' is not more a DATE than the previous one:
SQL> select to_date('10-JAN-19') from dual;
select to_date('10-JAN-19') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
You have to use TO_DATE specifying the format, and the language in this case or use only numbers.

Re: difference amount between 2 dates [message #674802 is a reply to message #674801] Mon, 18 February 2019 04:02 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Hi Michel,

the table 'my_table' is being loaded with input file data. the field KEY_NUMBER indicates the input file process number and its a auto generated number in my process.
hope, we can ignore this field value.

total 11 rows are inserted for the first run (i.e input file as 11 rows in it) and for the next run total 6 rows (i.e input file as 11 rows in it) are inserted.
and there will be any number of rows per a particular pay_date. In this scenario, pay_date '10-JAN-19' has 11 rows and pay_date '20-JAN-19' has 6 rows.

Requirement: The output will compare the accounts summarized totals from the previous data (pay_date = '10-JAN-2019') with the current data (pay_date = '20-JAN-2019').

Assume, my_table may have any number of pay_date values but we need to compare with 2 pay_dates always. generally, the comparison between latest pay_date and immediate previous pay_date of table.

we are using Oracle 12c database.

Thank you.



Re: difference amount between 2 dates [message #674804 is a reply to message #674802] Mon, 18 February 2019 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with
  2    precompute as (
  3      select account, sum(pay_amount) pay_amount, pay_date
  4      from my_table
  5      group by account, pay_date
  6    ),
  7    data as (
  8      select account, pay_amount,
  9             lead(pay_date) over (partition by account order by pay_date) pay_date_new,
 10             pay_date pay_date_old,
 11             lead(pay_amount,1,0) over (partition by account order by pay_date) pay_amount_new
 12      from precompute
 13    )
 14  select account, pay_amount pay_amount_old, pay_amount_new, pay_amount_new - pay_amount difference,
 15         to_char(100 * (pay_amount_new-pay_amount) / pay_amount, 'S990.00') || '%'
 16           "%DIFFERENCE"
 17  from data
 18  where pay_date_old = to_date('10/01/0019','DD/MM/YYYY')
 19  order by account, pay_date_new
 20  /
ACCOUNT    PAY_AMOUNT_OLD PAY_AMOUNT_NEW DIFFERENCE %DIFFERE
---------- -------------- -------------- ---------- --------
721017             150.25         100.25        -50  -33.28%
854793             165.33          140.5     -24.83  -15.02%
854895              51.52              0     -51.52 -100.00%
854897              72.85              0     -72.85 -100.00%
854898              51.52              0     -51.52 -100.00%
854899              72.85              0     -72.85 -100.00%
854900             -19.32         -19.32          0   +0.00%
854905              100.4           90.4        -10   -9.96%
854920                210              0       -210 -100.00%

9 rows selected.
Re: difference amount between 2 dates [message #674808 is a reply to message #674804] Mon, 18 February 2019 06:25 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
thank you Michel for the solution.

I observed that if account not existing in pay_date_old and exists in pay_date_new, then that account row should be in output results.
for example insert new row for existing rows with new account (not available for pay_date = '10-JAN-19') for pay_date '20-JAN-19'. See below insert statement for reference.
INSERT INTO PS_VG_TEST_58784 (key_number, pay_date, account, pay_amount) VALUES ('1102548', '20-JAN-19', '854921', 300.0);

with this new row insertion, our query is not returning this newly inserted row.

output should have this new row as well.
Account	 previous_amount(10-JAN-19)	current_amount(20-JAN-19)	difference  	 %difference
854921	     0                              300	                         300	          NA

I should get the output with all the available accounts in both pay_dates along with summarized total for the same accounts with different pay_date values.

thank you.

Re: difference amount between 2 dates [message #674809 is a reply to message #674808] Mon, 18 February 2019 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try to modify by yourself to get the result you want with the help of my first answer modifying its line 13.

[Updated on: Mon, 18 February 2019 06:33]

Report message to a moderator

Re: difference amount between 2 dates [message #674810 is a reply to message #674809] Mon, 18 February 2019 07:16 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Hi Michel,

tried by removing the 18th line, then multiple account rows are showing in the output.
And as per the new comment tried to modify the line 13, but no luck. please help me.

Thank you.

Regards
Suji
Re: difference amount between 2 dates [message #674811 is a reply to message #674810] Mon, 18 February 2019 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Show us what you tried.

Re: difference amount between 2 dates [message #674812 is a reply to message #674810] Mon, 18 February 2019 07:54 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Hi Michel,

added sub-query similar to lead function for pay_date_new, but still not showing the correct results.
INSERT INTO my_table (key_number, pay_date, account, pay_amount) VALUES ('1102548', '20-JAN-19', '854921', 300.0);

tried with union and lag function. but still not success with the results.
Could you please help me with modified query. Thank you.
Re: difference amount between 2 dates [message #674813 is a reply to message #674812] Mon, 18 February 2019 07:59 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
I tried with the below query:

with
      precompute as (
        select account, sum(pay_amount) pay_amount, pay_date
        from PS_VG_TEST_58784
        group by account, pay_date
      ),
      data as (
        select account, pay_amount,
               lead(pay_date) over (partition by account order by pay_date) pay_date_new,
              pay_date pay_date_old,
              lead(pay_amount,1,0) over (partition by account order by pay_date) pay_amount_new
       from precompute
       union
     select account, pay_amount,
               lag(pay_date) over (partition by account order by pay_date) pay_date_old,
              pay_date pay_date_new,
              lag(pay_amount,1,0) over (partition by account order by pay_date) pay_amount_old
              from precompute  
     )
   select account, pay_amount pay_amount_old, pay_amount_new, pay_amount_new - pay_amount difference,
          to_char(100 * (pay_amount_new-pay_amount) / pay_amount, 'S990.00') || '%'
            "%DIFFERENCE"
   from data
    where pay_date_old = to_date('10/01/2019','DD/MM/YYYY') and pay_date_old is not null 
    or (pay_date_new = to_date('20/01/2019','DD/MM/YYYY') and pay_date_new is not null )
   order by account, pay_date_new;
please help me with the changes. thank you.
Re: difference amount between 2 dates [message #674814 is a reply to message #674813] Mon, 18 February 2019 08:36 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Hi Michel,

requesting you please help me with modified sql to get all accounts for specific pay_dates.


Thank you.

Regards
Suji
Re: difference amount between 2 dates [message #674818 is a reply to message #674814] Mon, 18 February 2019 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I wish you had taken my remarks about dates into account and not only my queries.
It is not because I modified my environment to make your inserts work with me that you can be lazy and continue to post them as is.

[Updated on: Mon, 18 February 2019 10:50]

Report message to a moderator

Re: difference amount between 2 dates [message #674819 is a reply to message #674818] Mon, 18 February 2019 11:12 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Hi Michel,
Yes, I considered your remarks very seriously and followed them as well.
Insert statements with date worked well for me in my environment.

As you suggested, I tried a lot to get the expected results but some I did reach it.
I am sorry that you were assumed me lazy guy... FYI... I tried different ways but I was unable express myself. Hope you understood me.
Thank you.
Re: difference amount between 2 dates [message #674820 is a reply to message #674819] Mon, 18 February 2019 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Yes, I considered your remarks very seriously and followed them as well.

No, you didn't, I told yu to use TO_DATE you didn't.

Quote:
Insert statements with date worked well for me in my environment.

And I showed you it doesn't in mine and I told you I had to modify to make them to work.
I expect YOU modify your statements to work in ALL environments.

The time I spend to make them work for me is time I do not spend to solve your issue (which is clear by the way).

Re: difference amount between 2 dates [message #674821 is a reply to message #674820] Mon, 18 February 2019 11:48 Go to previous messageGo to next message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Thank you very much Michel.
Re: difference amount between 2 dates [message #674822 is a reply to message #674821] Mon, 18 February 2019 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this what you want?
SQL> with
  2    precompute as (
  3      select account, sum(pay_amount) pay_amount, pay_date
  4      from my_table
  5      group by account, pay_date
  6    ),
  7    data as (
  8      select account, pay_amount, pay_date,
  9             lag(pay_date) over (partition by account order by pay_date) pay_date_prev,
 10             lag(pay_amount,1,0) over (partition by account order by pay_date) pay_amount_prev,
 11             row_number() over (partition by account order by pay_date) rn,
 12             count(*) over (partition by account) cnt
 13      from precompute
 14    ),
 15    minmaxdt as (select min(pay_date) min_date, max(pay_date) max_date from my_table),
 16    new_data as (
 17      select account,
 18             case
 19               when rn = 1 then decode(pay_date, min_date,pay_date, to_date(null))
 20               else pay_date_prev
 21             end pay_date_old,
 22             case
 23               when rn = 1 then decode(pay_date, min_date,to_date(null), pay_date)
 24               else pay_date
 25             end pay_date_new,
 26             case
 27               when rn = 1 then decode(pay_date, min_date,pay_amount, 0)
 28               else pay_amount_prev
 29             end pay_amount_old,
 30             case
 31               when rn = 1 then decode(pay_date, min_date,0, pay_amount)
 32               else pay_amount
 33             end pay_amount_new
 34      from data, minmaxdt
 35      where rn > 1 or rn = cnt
 36    )
 37  select account, pay_date_old, pay_date_new, pay_amount_old, pay_amount_new,
 38         pay_amount_new-pay_amount_old difference,
 39         decode(pay_amount_old,
 40                0,'N/A',
 41                to_char(100 * (pay_amount_new-pay_amount_old) / pay_amount_old, 'S990.00') || '%'
 42               ) "%DIFFERENCE"
 43  from new_data
 44  order by account, pay_date_old nulls first
 45  /
ACCOUNT    PAY_DATE_OL PAY_DATE_NE PAY_AMOUNT_OLD PAY_AMOUNT_NEW DIFFERENCE %DIFFERE
---------- ----------- ----------- -------------- -------------- ---------- --------
721017     10-JAN-0019 20-JAN-0019         150.25         100.25        -50  -33.28%
854793     10-JAN-0019 20-JAN-0019         165.33          140.5     -24.83  -15.02%
854795                 20-JAN-0019              0          51.52      51.52 N/A
854797                 20-JAN-0019              0          50.25      50.25 N/A
854895     10-JAN-0019                      51.52              0     -51.52 -100.00%
854897     10-JAN-0019                      72.85              0     -72.85 -100.00%
854898     10-JAN-0019                      51.52              0     -51.52 -100.00%
854899     10-JAN-0019                      72.85              0     -72.85 -100.00%
854900     10-JAN-0019 20-JAN-0019         -19.32         -19.32          0   +0.00%
854905     10-JAN-0019 20-JAN-0019          100.4           90.4        -10   -9.96%
854920     10-JAN-0019                        210              0       -210 -100.00%
854921                 20-JAN-0019              0            300        300 N/A

12 rows selected.
It also should work if you have more than 2 dates for some or all accounts (then the pay_date_old in the ORDER BY).

[Updated on: Mon, 18 February 2019 12:07]

Report message to a moderator

Re: difference amount between 2 dates [message #674914 is a reply to message #674822] Thu, 21 February 2019 12:52 Go to previous message
suji6281
Messages: 106
Registered: September 2014
Senior Member
Thank you Michel
Previous Topic: How to Update the Col2 for below Table Structure
Next Topic: Parallel DDL statements in a same table with different partitions
Goto Forum:
  


Current Time: Mon Sep 28 11:12:18 CDT 2020