CODEWITHSUNDEEP
sqloracle10g
Stanley Mungai
Stanley Mungai

Reputation: 4150

Get the value from the first date and the last date sql

I have a table that store client Balances after every transaction they do in this format:

Client number    balance     tran_date
7734766688       23000       07-AUG-2014
7734766688       40000       07-AUG-2014
7734766688       20000       10-AUG-2014
7734766688       13000       15-AUG-2014
7734766688      400000       29-AUG-2014
7734766688      200000       02-SEP-2014

No the Client want a Statement between 12th AUGUST and 31st of AUGUST which means that the opening balance is 20000 and the closing balance as at 31st AUG is 400000 How can I write a query to get the Balances as at the queried date and the last date given that the date available is only as per the last transaction?

I have tried this to get the Opening Balance but it does not seem Sufficient:

select balance from (select * from  client_balances where client_number = '7734766688' 
order by TRAN_DATE asc) 
where TRAN_DATE >= '01-AUG-2014' and rownum =1;

Please help, am stuck.

Upvotes: 0

Views: 74

Answers (1)

Multisync
Multisync

Reputation: 8787

with client_balances as (
select '7734766688' client_number, 23000 balance, to_date('07-08-2014', 'DD-MM-YYYY') tran_date from dual
union all select '7734766688', 40000, to_date('07-08-2014', 'DD-MM-YYYY') from dual
union all select '7734766688', 20000, to_date('10-08-2014', 'DD-MM-YYYY') from dual
union all select '7734766688', 13000, to_date('15-08-2014', 'DD-MM-YYYY') from dual
union all select '7734766688', 400000, to_date('29-08-2014', 'DD-MM-YYYY') from dual
union all select '7734766688', 200000, to_date('02-09-2014', 'DD-MM-YYYY') from dual
),
client_balances_analytic as (
select client_number, balance, tran_date, 
       lag(balance) over(partition by client_number order by tran_date) prev_balance,
       lag(tran_date) over(partition by client_number order by tran_date) prev_tran_date
from client_balances)
select tran_date_start, balance_start, tran_date_end, balance_end from (
  select case when tran_date = to_date('12-08-2014', 'DD-MM-YYYY') then tran_date else nvl(prev_tran_date, tran_date) end tran_date_start, 
         case when tran_date = to_date('12-08-2014', 'DD-MM-YYYY') then balance else nvl(prev_balance, balance) end balance_start, 
         row_number() over(order by tran_date) rw,
         last_value(tran_date) over(order by tran_date rows between current row and unbounded following) tran_date_end,
         last_value(balance) over(order by tran_date rows between current row and unbounded following) balance_end
   from client_balances_analytic 
  where client_number = '7734766688' and tran_date between to_date('12-08-2014', 'DD-MM-YYYY') and to_date('31-08-2014', 'DD-MM-YYYY')
) where rw = 1;

lag function retrieves the data from previous row

this query may not work properly with ties (the same tran_dates). In this case you need some extra criteria to sort transactions (in order to get the earliest)

Upvotes: 1

Related Questions