case when用法

最新推荐文章于 2023-03-23 16:25:01 发布

ko_oi

最新推荐文章于 2023-03-23 16:25:01 发布

阅读量273

点赞数

分类专栏： SQL Server 文章标签：数据库 sqlserver

本文链接：https://blog.csdn.net/ko_oi/article/details/103458320

版权

SQL Server 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

select t1.AcctCode,t1.AcctName,t0.shortname,t3.cardname,
sum(case when t2.RefDate<'[%0]' then t0.Debit-t0.Credit else 0 end ) as 期初余额,
sum(case when t2.RefDate >='[%0]' and t2.RefDate <= '[%1]' then t0.Debit else 0 end ) as 本期借方,
sum(case when t2.RefDate >='[%0]' and t2.RefDate <='[%1]' then t0.Credit else 0 end ) as 本期贷方,
sum(case when t2.RefDate<='[%1]' then t0.Debit-t0.Credit else 0 end ) as 期末余额,
sum(t0.Debit-t0.Credit) as '余额'
from JDT1 t0
inner join OACT t1 on t1.AcctCode=t0.Account
inner join OJDT t2 on t2.transid=t0.TransId
inner join OCRD t3 on t0.shortname=t3.cardcode
where t2.RefDate >='[%0]' and t2.RefDate <='[%1]'
and (t1.AcctCode ='230201' or t1.AcctCode ='230205')
group by t1.AcctCode,t1.AcctName,t0.shortname,t3.cardname