CODEWITHSUNDEEP
sqloracle-database
navid sedigh
navid sedigh

Reputation: 281

calculate different between a column of a table in two days

I have a table like below in oracle database:

s_num     eff_date     amount
1         07-OCT-18      110
1         06-OCT-18      50
2         07-OCT-18      200
2         06-OCT-18      150

I want to find the different between "amount" column for today and yesterday. and the result must be like below

s_num    diff
1         60
2         50

I want to do this without join the table with itself.

Upvotes: 1

Views: 64

Answers (2)

Ganesh Chowdhary Sadanala
Ganesh Chowdhary Sadanala

Reputation: 1278

sql>set serveroutput on;
sql>create or replace function difference(n in number) return number as di 
    number(5);
 2  begin
 3  select ((select amount from oc where dob=trunc(sysdate) and snum=n)- 
    (select 
    amount from oc where dob=trunc(sysdate-1) and snum=n)) into di from dual;
 4  return di;
 5  end;
 6  /
 sql>select distinct snum,difference(snum) as diff from oc;

output:

                   SNUM       DIFF
            ---------- ----------
                    2         50
                    1         60

Upvotes: -1

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

you may use conditional aggregation 

SELECT s_num,SUM (CASE 
                    WHEN eff_date >= TRUNC(sysdate) 
                       AND eff_date < TRUNC(sysdate) + 1 THEN amount --today
                    WHEN eff_date >= TRUNC(sysdate) - 1 
                       AND eff_date < TRUNC(sysdate) THEN - amount --yesterday
                END) 
    FROM   t 
    GROUP  BY s_num;

Or LAG function

SELECT s_num, 
       diff 
FROM   (SELECT s_num, 
               eff_date,
               amount - LAG(amount, 1, 0) 
                          OVER ( 
                            partition BY s_num 
                            ORDER BY eff_date ) AS diff 
        FROM   t) 
WHERE  eff_date >= trunc(sysdate) 
       AND eff_date < trunc(sysdate) + 1 ;

Demo

The first one will print NULL difference for days other than today, the second one does not return any rows for other dates.

Upvotes: 5

Related Questions