Oracle Query For opening and closing balance [clos

问题:

hi i have following table and data to get opening and closing balance of every account opening balance required gdate<02-oct-2013 here is my table

 create table ledger (account_no varchar2(10),gdate date,debit number(8),credit number(8))

insert into ledger (account_no,gdate,debit,credit) values ('10-0001','01-oct-2013',1000,0);
  insert into ledger (account_no,gdate,debit,credit) values ('10-0001','24-oct-2013',0,440);
insert into ledger (account_no,gdate,debit,credit) values ('20-0001','01-oct-2013',3000,0);
insert into ledger (account_no,gdate,debit,credit) values ('30-0001','01-oct-2013',300,0);
insert into ledger (account_no,gdate,debit,credit) values ('20-0001','16-oct-2013',1200,0);
insert into ledger (account_no,gdate,debit,credit) values ('30-0001','17-oct-2013',0,1340);
insert into ledger (account_no,gdate,debit,credit) values ('30-0001','24-oct-2013',500,0);

i need following result

 ACCOUNT_NO OPENING  DEBIT  CREDIT  CLOSING
  10-0001    1000    0       440    560
  20-0001    3000    1200      0    4200
  30-0001     300    500    1340    540

回答1:

This is more of a comment because it doesn't answer the question. Your query simplifies to:

Select account_no, LAG(closing, 1 ,0) OVER (order by account_no) as opening,
       debit, credit,
       (LAG(closing,1,0) OVER (order by account_no )+ closing) as closing
from (select account_no, 0 as OPEN, SUM(debit) as debit, SUM(credit) as credit,
             sum(debit) - sum(credit) as closing
      FROM ledger
      where gdate > '20-oct-13'
      group by account_no
     ) a;

Some notes. order by in a subquery or CTE is not useful, unless you are selecting rownum in the next outer query. If you want the results ordered, then put an order by in the outer query.

The query itself doesn't make sense. Why would the opening balance come from the previous account number? Usually such terminology is used for dates on a particular account, and you have eliminated all dates in the group by clause.

回答2:

You keep changing your requirements, but based on what you shown at the moment, this works:

select account_no,
 max(opening) keep (dense_rank first order by gdate) as opening,
 sum(debit) as debit,
 sum(credit) as credit,
 max(closing) keep (dense_rank first order by gdate desc) as closing
from (
 select account_no, gdate, credit, debit,
 lag(balance, 1, 0) over (partition by account_no order by gdate) as opening,
 balance as closing
 from (
  select account_no, gdate, debit, credit,
  sum(debit) over (partition by account_no order by gdate) as sum_debit,
  sum(credit) over (partition by account_no order by gdate) as sum_credit,
  sum(credit) over (partition by account_no order by gdate)
   - sum(debit) over (partition by account_no order by gdate) as balance
  from ledger
 )
)
where gdate > date '2013-10-02'
group by account_no
order by account_no;

Which is the same logic I linked to before, when you had different data. SQL Fiddle.

I'm not sure why you're showing your opening balance as positive though; seems wrong when all you have are debits. If that really is what you want then just swap how the balance is calculated:

...
  sum(debit) over (partition by account_no order by gdate)
   - sum(credit) over (partition by account_no order by gdate) as balance
...

SQL Fiddle.