Oracle Query，用于期初和期末余额 [英] Oracle Query For opening and closing balance

问题描述

我有下表和数据来获取所需的每个帐户开户余额的期初余额和期末余额gdate <2013年10月2日 这是我的桌子

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);

我需要以下结果

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

推荐答案

您一直在更改自己的要求，但是根据您当前显示的内容，这可行:

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;

当您具有不同的数据时，这与我之前链接的逻辑相同. SQL小提琴.

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小提琴.

这篇关于Oracle Query，用于期初和期末余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！