我现在要按项目统计每年每月各个会计科目的借贷情况。
图中贷方(credit)之前的列都是表中的列,u_docnum是会计凭证编号。其他都是派生列。黄色框内是通过row_number函数获得列,debitsum是debit逐行累加的结果,同理creditsum是credit逐行累加结果。
代码如下:
----------------------------
/*项目明细账代码*/ -- 增加docnum
with xmmxz (year,month,prjname,account,acctname,debit,credit,Row_Num,u_docnum)
as
(
select year(t0.refdate) as 'year',month(t0.refdate) as 'Month',
t3.prjname,t1.account,t2.acctname,t1.debit,t1.credit,
row_number()over(partition by t3.prjname,t1.account,t2.acctname,year(t0.refdate),month(t0.refdate)
order by t0.u_docnum) as Row_Num --获得序号
,t0.u_docnum from ojdt t0 inner join jdt1 t1 on t1.transid=t0.transid
inner join oact t2 on t2.acctcode=t1.account
left join oprj t3 on t3.prjcode=t1.project
where t3.prjname is not null and t3.prjname<>'other' --测试使用,实际应用时and t3.prjname<>'other'不加
)
select *,(case
when debitsum-creditsum > 0 then '借'
when debitsum-creditsum = 0 then '平'
else '贷'
end) as '方向'
from
(select t2.year,t2.month,prjname,account,acctname,cast(debit as numeric(10,3)) as debit
,cast(credit as numeric(10,3)) as credit,Row_Num,u_docnum
,(select cast(sum(debit) as numeric(10,3))
from xmmxz t1
where t1.Row_Num<=t2.Row_Num and t1.year=t2.year and t1.month=t2.month and t1.prjname=t2.prjname
and t1.account=t2.account and t1.acctname=t2.acctname) as debitsum --通过自连接(序号)实现累加
,(select cast(sum(t1.credit) as numeric(10,3))
from xmmxz t1
where t1.Row_Num<=t2.Row_Num and t1.year=t2.year and t1.month=t2.month and t1.prjname=t2.prjname
and t1.account=t2.account and t1.acctname=t2.acctname) as creditsum
from xmmxz as t2) as ttt
---------------------------
产生结果如图,但是图中红色框内不是逐行累加,(红色框位置有点不对,各位应该看得懂的),而绿色框内就是逐行累加的。不知道为什么。