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 |
|
suji6281
Messages: 135 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 |
Solomon Yakobson
Messages: 3275 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 |
|
Michel Cadot
Messages: 68665 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 #674799 is a reply to message #674781] |
Mon, 18 February 2019 02:54 |
|
suji6281
Messages: 135 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.
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 #674804 is a reply to message #674802] |
Mon, 18 February 2019 04:27 |
|
Michel Cadot
Messages: 68665 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 #674813 is a reply to message #674812] |
Mon, 18 February 2019 07:59 |
|
suji6281
Messages: 135 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 #674822 is a reply to message #674821] |
Mon, 18 February 2019 11:59 |
|
Michel Cadot
Messages: 68665 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
|
|
|
|
Goto Forum:
Current Time: Mon Jul 01 19:34:04 CDT 2024
|