CODEWITHSUNDEEP
sqldatabaseoracle-databaseanalytics
user2891092
user2891092

Reputation: 163

oracle query balance

I want to select the balance of the last 2 days(today and yesterday) which the code look something like this

select a.balance as "today",b.balance as "yesterday"
from account a,account b
where a.id='xxx'
and a.id=b.id
and a.dates=to_date(sysdate, 'DD-MM-YYYY') --today
and b.dates=to_date(sysdate-1, 'DD-MM-YYYY') --yesterday

the problem came when the data for today has not been inputted yet. this result the two balance null even though yesterday data is available

note: my current solution is to split the query into 2. but i am hoping there is any ways to use only 1 query

expected output

-----------------
|today|yesterday|
-----------------
|null |9000     |
-----------------

Data

--------------------------
|id   |balance  |dates   |
--------------------------
|1    |9000     |6/5/2015|
--------------------------

Upvotes: 1

Views: 789

Answers (3)

Pratsam
Pratsam

Reputation: 162

No need to join, use LAG function to track previous.
If you would like to know about Lag function. Please visit below link. http://www.techonthenet.com/oracle/functions/lag.php. I have taken below as input. enter image description here

and executed below query using lag which automatically tracks previous row.
SELECT * FROM( SELECT ID,LAG(BALANCE) OVER (ORDER BY DATES) AS YESTERDAY_BALANCE,BALANCE AS TODAYS_BALANCE FROM ACCOUNTS) WHERE YESTERDAY_BALANCE IS NOT NULL;

Output which I got is below. If you wont get data for today still it will display the row.
enter image description here

Upvotes: 3

Gaurav Soni
Gaurav Soni

Reputation: 6336

No need to join two tables ,if we are allowed to hard code two dates 

--code with sysdate 

with tab as  --dummy data
(
select 1 id,sysdate -level+1 dat,level*1000 balance 
from dual 
connect by     level <=10  
)
--main query
select max(decode(trunc(dat),trunc(SYSDATE),balance)) "today"
      ,max(decode(trunc(dat),trunc(SYSDATE-1),balance)) "yesterday"
from tab t where TRUNC(t.dat) IN (TRUNC(SYSDATE),TRUNC(SYSDATE-1));

--code without sysdate

with tab as
(
 select 1 id,sysdate -level dat,level*1000 balance 
 from dual 
 connect by level <=10
 )
 --main query
 select max(decode(trunc(dat),trunc(SYSDATE),balance)) "today"
      ,max(decode(trunc(dat),trunc(SYSDATE-1),balance)) "yesterday"
from tab t where TRUNC(t.dat) IN (TRUNC(SYSDATE),TRUNC(SYSDATE-1));

sqlfiddle

select 
 max(decode(trunc(dates),trunc(SYSDATE),balance)) "today"
,max(decode(trunc(dates),trunc(SYSDATE-1),balance)) "yesterday"
from account a
where a.id='xxx'
and trunc(a.dates) IN (trunc(sysdate),trunc(sysdate-1));

Upvotes: 2

LoztInSpace
LoztInSpace

Reputation: 5697

I think you are asking for yesterday OUTER JOINED onto today.

select a.balance as "today",b.balance as "yesterday"
from account a,account b
where b.id='xxx'
and a.id(+) =b.id  -- <----Here
and a.dates(+)=to_date(sysdate, 'DD-MM-YYYY') --today
and b.dates=to_date(sysdate-1, 'DD-MM-YYYY') --yesterday

I'm a bit rusty on that old syntax, In more modern parlance

select a.balance as "today",b.balance as "yesterday"
from account b LEFT OUTER JOIN account a on
a.id=b.id and a.dates=to_date(sysdate, 'DD-MM-YYYY') --today
where b.id='xxx'
and b.dates=to_date(sysdate-1, 'DD-MM-YYYY') --yesterday

Notice how I've moved the checking of the optional table (a) to the join condition so it comes across anyway.

Upvotes: 1

Related Questions