1、需求背景

财务想做通过选择一个日期，累计至所选日期统计出不同部门收入、支出的收款、业务成本、其他支出、净收支的人民币金额数据。

image.png

2、难点

1) 部门如何维护。--辅助资料列表–事业部中维护

2) 何为业务成本？其他成本？

根据费用项目备注来判断
 
--其他成本
T_BD_EXPENSE_L.FDESCRIPTION not like '01、业务成本'
--业务成本
T_BD_EXPENSE_L.FDESCRIPTION like '01、业务成本'

3)付款表读取付款明细表的事业部，收款表读取单据头的事业部

T_AP_PAYBILLENTRY.F_kd_Assistant_SYB =  T_BAS_ASSISTANTDATAENTRY_L.FENTRYID  --付款明细表 与辅助信息明细表（找事业部）连接
T_AR_RECEIVEBILL.F_SYB_ASSISTANT = T_BAS_ASSISTANTDATAENTRY_L.FENTRYID --收款单表【事业部】 与辅助信息明细表（找事业部）连接

4)汇率取当月汇率

left join #HL
on SK.FCURRENCYID = #HL.YB
and  convert(char(7),#HL.BDATE, 120) = convert(char(7), SK.FDATE, 120),

5)最后一行显示合计

 case when A2 is not null then A2 else '合计' end A2

SQL源码

select X.FBEGDATE as BDATE,X.FCYFORID as YB, X.FEXCHANGERATE as ZJHL 
into #HL
from T_BD_Rate X
where convert(char(4), X.FBEGDATE, 120) = convert(char(4), '#BDate#', 120)
and X.FRATETYPEID = '8355919'


--收款临时表
select SK.FDATE as A1 
,SYB.FDATAVALUE A2
,SYB.FDESCRIPTION A4

,(case when SK.FCURRENCYID = '1' 
then  
     SKMX.FREALRECAMOUNTFOR  
else 
     SKMX.FREALRECAMOUNTFOR * #HL.ZJHL
end) as A5

into #SKSUM
from T_AR_RECEIVEBILL as SK
left join #HL 
on SK.FCURRENCYID = #HL.YB
and  convert(char(7),#HL.BDATE, 120) = convert(char(7), SK.FDATE, 120),
T_AR_RECEIVEBILLENTRY as SKMX, 
T_CN_RECPAYPURPOSE_L as JCZL,
T_BAS_ASSISTANTDATAENTRY_L as SYB
where SK.FID = SKMX.FID
and SKMX.FPURPOSEID = JCZL.FID
and SK.F_SYB_ASSISTANT = SYB.FENTRYID 
and JCZL.FLOCALEID = '2052'
and JCZL.FNAME not Like '%调拨%'
and SK.FDOCUMENTSTATUS = 'C'
and SK.FCANCELSTATUS = 'A'
and datepart(yy,SK.FDATE) = datepart(yy, '#BDate#')

select 
FK.FDATE as A1 
,SYB.FDATAVALUE A2 
,SYB.FDESCRIPTION A4

,(case when FK.FCURRENCYID = '1'
then
    FKMX.FREALPAYAMOUNTFOR
else
    FKMX.FREALPAYAMOUNTFOR * #HL.ZJHL
end) as A5

into #YWCB
from T_AP_PAYBILL as FK
left join #HL 
on FK.FCURRENCYID = #HL.YB
and  convert(char(7),#HL.BDATE, 120) = convert(char(7), FK.FDATE, 120)

,T_AP_PAYBILLENTRY as FKMX
,T_BAS_ASSISTANTDATAENTRY_L as SYB
,T_BD_EXPENSE_L as FY

where  FK.FID = FKMX.FID
and FKMX.F_kd_Assistant_SYB = SYB.FENTRYID 
and FKMX.FCOSTID = FY.FEXPID
and FK.FDOCUMENTSTATUS = 'C'
and FK.FCANCELSTATUS = 'A'
and FKMX.FCOSTID <>''
and FY.FDESCRIPTION like '01、业务成本'
and datepart(yy,FK.FDATE) = datepart(yy, '#BDate#')



--其他支出明细临时表
select FK.FDATE as A1 
,SYB.FDATAVALUE A2 
,SYB.FDESCRIPTION A4 
,(case when FK.FCURRENCYID = '1'
then
    FKMX.FREALPAYAMOUNTFOR
else
    FKMX.FREALPAYAMOUNTFOR * #HL.ZJHL
end) as A5

into #QTFK
from T_AP_PAYBILL as FK
left join #HL 
on FK.FCURRENCYID = #HL.YB
and  convert(char(7),#HL.BDATE, 120) = convert(char(7), FK.FDATE, 120)

,T_AP_PAYBILLENTRY as FKMX
,T_BAS_ASSISTANTDATAENTRY_L as SYB
,T_BD_EXPENSE_L as FY
where  FK.FID = FKMX.FID
and FKMX.F_kd_Assistant_SYB = SYB.FENTRYID
and FKMX.FCOSTID = FY.FEXPID
and FK.FDOCUMENTSTATUS = 'C'
and FK.FCANCELSTATUS = 'A'
and FKMX.FCOSTID <>''
and FY.FDESCRIPTION not like '%01、业务成本%'
and FY.FDESCRIPTION <>''
and datepart(yy,FK.FDATE) = datepart(yy, '#BDate#')



--查询需要统计事业部备注 #SYB
select FDATAVALUE A1 --事业部
,FDESCRIPTION A2--备注
into #SYB
from T_BAS_ASSISTANTDATAENTRY a,T_BAS_ASSISTANTDATAENTRY_L b 
where a.FID = '59832ad438268e'
and a.FMASTERID =b.FENTRYID
and FDESCRIPTION <>''
order by FDESCRIPTION

-----------组装起来------------------
select
 case when A2 is not null then A2 else '合计' end A2
,SUM(SBDLJ)
,SUM(FBDLJ)
,SUM(F2BDLJ)
,SUM(SUMBDLJ)

,SUM(SBWLJ)
,SUM(FBWLJ)
,SUM(F2BWLJ)
,SUM(SUMBWLJ)

,SUM(SBMLJ)
,SUM(FBMLJ)
,SUM(F2BMLJ)
,SUM(SUMBMLJ)

,SUM(SBYLJ)
,SUM(FBYLJ)
,SUM(F2BYLJ)
,SUM(SUMBYLJ)

from(
select 
#SYB.A2 as A2--备注
--,A1 --事业部

,(case when SBDLJ is not null then SBDLJ else '0' end ) as SBDLJ  
,(case when FBDLJ is not null then FBDLJ else '0' end ) as FBDLJ  
,(case when F2BDLJ is not null then F2BDLJ else '0' end ) as F2BDLJ
,(case when SBDLJ is not null then SBDLJ else '0' end ) 
-(case when FBDLJ is not null then FBDLJ else '0' end ) 
-(case when F2BDLJ is not null then F2BDLJ else '0' end ) as SUMBDLJ


,(case when SBWLJ is not null then SBWLJ  else '0' end ) as SBWLJ  
,(case when FBWLJ is not null then FBWLJ else '0' end ) as FBWLJ  
,(case when F2BWLJ is not null then F2BWLJ else '0' end ) as F2BWLJ
,(case when SBWLJ is not null then SBWLJ  else '0' end )  
-(case when FBWLJ is not null then FBWLJ else '0' end )
-(case when F2BWLJ is not null then F2BWLJ else '0' end ) as SUMBWLJ


,(case when SBMLJ is not null then SBMLJ else '0' end ) as SBMLJ  
,(case when FBMLJ is not null then FBMLJ else '0' end ) as FBMLJ  
,(case when F2BMLJ is not null then F2BMLJ else '0' end ) as F2BMLJ
,(case when SBMLJ is not null then SBMLJ else '0' end ) 
-(case when FBMLJ is not null then FBMLJ else '0' end )
-(case when F2BMLJ is not null then F2BMLJ else '0' end ) as SUMBMLJ

,(case when SBYLJ is not null then SBYLJ else '0' end ) as SBYLJ  
,(case when FBYLJ is not null then FBYLJ else '0' end ) as FBYLJ  
,(case when F2BYLJ is not null then F2BYLJ else '0' end ) as F2BYLJ
,(case when SBYLJ is not null then SBYLJ else '0' end )  
-(case when FBYLJ is not null then FBYLJ else '0' end )
-(case when F2BYLJ is not null then F2BYLJ else '0' end ) as SUMBYLJ


from #SYB

LEFT JOIN 
(
--本日收款
select A2,sum(A5) as SBDLJ
from #SKSUM 
where convert(char(10), A1, 120) = '#BDate#'
group by A2
) as SBDLJ
 ON #SYB.A1 = SBDLJ.A2

LEFT JOIN 
(
--本周收款
select A2,sum(A5) as SBWLJ
from #SKSUM 
where convert(char(10), A1, 120) < dateadd(day, 1, '#BDate#')     --小于查询日期范围  
and datepart(mm,A1) = datepart(mm,'#BDate#')    --是当前月
and datepart(WW,A1) = datepart(WW, '#BDate#')   --开始查周
group by A2
) as SBWLJ
 ON #SYB.A1 = SBWLJ.A2

LEFT JOIN 
(
--本月收款
select A2,sum(A5) as SBMLJ
from #SKSUM
where convert(char(10), A1, 120) < dateadd(day, 1, '#BDate#')     --查询日期范围
and datepart(mm,A1) = datepart(mm,'#BDate#')

group by A2
) as SBMLJ
 ON #SYB.A1 = SBMLJ.A2

LEFT JOIN 
(
--本年收款
select A2,sum(A5) as SBYLJ
from #SKSUM 
where  convert(char(10), A1, 120) < dateadd(day, 1, '#BDate#')     --查询日期范围
and datepart(yy,A1) = datepart(yy, '#BDate#')
group by A2
) as SBYLJ 
ON #SYB.A1 = SBYLJ.A2
--------业务支出---------
LEFT JOIN 
(
--本日业务支出
select A2,sum(A5) as FBDLJ
from #YWCB 
where convert(char(10), A1, 120) = '#BDate#'
group by A2
) as FBDLJ
ON #SYB.A1 = FBDLJ.A2

LEFT JOIN 
(
--本周业务支出
select A2,sum(A5) as FBWLJ
from #YWCB 
where convert(char(10), A1, 120) < dateadd(day, 1, '#BDate#')     --小于查询日期范围  
and datepart(mm,A1) = datepart(mm,'#BDate#')    --是当前月
and datepart(WW,A1) = datepart(WW, '#BDate#')   --开始查周
group by A2
) as FBWLJ
 ON #SYB.A1 = FBWLJ.A2

LEFT JOIN 
(
--本月业务支出
select A2,sum(A5) as FBMLJ
from #YWCB
where convert(char(10), A1, 120) < dateadd(day, 1, '#BDate#')     --查询日期范围
and datepart(mm,A1) = datepart(mm,'#BDate#')

group by A2
) as FBMLJ
 ON #SYB.A1 =  FBMLJ.A2

LEFT JOIN 
(
--本年支出
select A2,sum(A5) as FBYLJ
from #YWCB 
where  convert(char(10), A1, 120) < dateadd(day, 1, '#BDate#')     --查询日期范围
and datepart(yy,A1) = datepart(yy, '#BDate#')
group by A2
) as FBYLJ 
ON #SYB.A1 = FBYLJ.A2

---其他支出---------
LEFT JOIN 
(
--本日其他支出
select A2,sum(A5) as F2BDLJ
from #QTFK 
where convert(char(10), A1, 120) = '#BDate#'
group by A2
) as F2BDLJ
ON #SYB.A1 = F2BDLJ.A2

LEFT JOIN 
(
--本周其他支出
select A2,sum(A5) as F2BWLJ
from #QTFK 
where convert(char(10), A1, 120) < dateadd(day, 1, '#BDate#')     --小于查询日期范围  
and datepart(mm,A1) = datepart(mm,'#BDate#')    --是当前月
and datepart(WW,A1) = datepart(WW, '#BDate#')   --开始查周
group by A2
) as F2BWLJ
 ON #SYB.A1 = F2BWLJ.A2

LEFT JOIN 
(
--本月业务支出
select A2,sum(A5) as F2BMLJ
from #QTFK 
where convert(char(10), A1, 120) < dateadd(day, 1, '#BDate#')     --查询日期范围
and datepart(mm,A1) = datepart(mm,'#BDate#')

group by A2
) as F2BMLJ 
 ON #SYB.A1 = F2BMLJ.A2

LEFT JOIN 
(
--本年支出
select A2,sum(A5) as F2BYLJ
from #QTFK  
where  convert(char(10), A1, 120) < dateadd(day, 1, '#BDate#')     --查询日期范围
and datepart(yy,A1) = datepart(yy, '#BDate#')
group by A2
) as F2BYLJ 
ON #SYB.A1 = F2BYLJ.A2

) A
group by A2 with rollup
having grouping(A2) = 0 or grouping(A2) = 1 --合计行