本人产品，sql小白。

在同事网友指导下，做出一点小模样。晒晒，求交流更好方案。

财务需求如下表：——资金滚动月报

image

财务想做通过选择一个日期，累计至所选日期统计出收入、支出的不同用途下的实际收付款人民币金额数据。
【难点】

1、收支分类如何维护？——在金蝶K3中 收款读取收付款用途，支出读取费用项目，同时二级标题中加入数字来做排序。
2、外币统一转化成人民币？——读取每个月的汇率，计算出人民币金额

--汇率临时表
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'
 
 
--收款临时表
 
left join #HL
on SK.FCURRENCYID = #HL.YB
and  convert(char(7),#HL.BDATE, 120) = convert(char(7), SK.FDATE, 120),

3、根据所选日期，当月1日是周三时，本周统计>本月统计，如何解决？

--本周收款
select (case when sum(A5) is not null then sum(A5) else '0' end ) as BWLJ
into #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#')   --开始查周

表结构：

image.png

解决方案：
搭建：采用临时表存结果数据，最后完成组装。

--定义变量
DECLARE @BDate Datetime; --业务日期
SET @BDate= '#BDate#';

--汇率临时表 #HL
select X.FCYFORID as YB, X.FEXCHANGERATE as ZJHL into #HL
from T_BD_Rate X
where convert(char(7), X.FBEGDATE, 120) = convert(char(7), @BDate, 120)
and X.FRATETYPEID = '8355919'

--收付款用途临时表 #YT
select
 JCZL.FDESCRIPTION1  as A2 
,JCZL.FDESCRIPTION as A3
,JCZL.FNAME as A4
into #YT
from T_CN_RECPAYPURPOSE_L as JCZL
where JCZL.FLOCALEID = '2052'
and JCZL.FNAME not Like '%调拨%'
order by A4

--费用项目id临时表 #FYMX
select a.FEXPID A1 
,c.FNAME A2 --一级收支类别:经营性/非经营
,a.FDESCRIPTION A3 --二级收支类别：业务收入等
,a.FNAME A4    --费用项目明细
into  #FYMX
from T_BD_EXPENSE_L as a,T_BD_EXPENSE as b,T_BD_EXPENSE_GROUP_L as c
where a.FEXPID=b.FEXPID
--and b.FGROUP = c.FID
and a.FLOCALEID = '2052'

--收款临时表 #SKSUM
select
--SK.FBILLNO as A0,
SK.FDATE as A1 
,JCZL.FDESCRIPTION1  as A2 
,JCZL.FDESCRIPTION as A3
,JCZL.FNAME as 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,

T_AR_RECEIVEBILLENTRY as SKMX, 
T_CN_RECPAYPURPOSE_L as JCZL

where SK.FID = SKMX.FID
and SKMX.FPURPOSEID = JCZL.FID
and JCZL.FLOCALEID = '2052'
and JCZL.FNAME not Like '%调拨%'
and SK.FDOCUMENTSTATUS = 'C'
and convert(char(10), A1, 120) < dateadd(day, 1, @BDate)

--支出明细临时表
select  
FK.FDATE as A1 --业务日期
--,#FYMX.A2 as A2  --一级收支类别:经营性/非经营
--,#FYMX.A3 as A3  --二级收支类别：业务收入等
,#FYMX.A4 as A4  --费用项目明细

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

into #FKSUM
from T_AP_PAYBILL as FK
left join #HL on FK.FCURRENCYID = #HL.YB
,T_AP_PAYBILLENTRY as FKMX
left join #FYMX on FKMX.FCOSTID = #FYMX.A1
where  FK.FID = FKMX.FID
and FK.FDOCUMENTSTATUS = 'C'
and FKMX.FCOSTID <>''
and #FYMX.A4 <>''

2、开始组装

--收款组装起来
select TOP 100 PERCENT '收款' as A1 
,A2
--,A3
,A4
,sum(BDLJ) '本日累计'
,sum(BWLJ) '本周累计'
,sum(BMLJ) '本月累计'
,sum(BYLJ) '本年累计'
,sum(LJ01) '1月'
,sum(LJ02) '2月'
,sum(LJ03) '3月'
,sum(LJ04) '4月'
,sum(LJ05) '5月'
,sum(LJ06) '6月'
,sum(LJ07) '7月'
,sum(LJ08) '8月'
,sum(LJ09) '9月'
,sum(LJ10) '10月'
,sum(LJ11) '11月'
,sum(LJ12) '12月'

  from (

SELECT '收款' as A1 
,#YT.A2 as A2
--,#YT.A3 as A3
,#YT.A4 as A4
,(case when BDLJ is not null then BDLJ else '0' end ) as BDLJ  
,(case when BWLJ is not null then BWLJ else '0' end ) as BWLJ 
,(case when BMLJ is not null then BMLJ else '0' end ) as BMLJ 
,(case when BYLJ is not null then BYLJ else '0' end ) as BYLJ
,(case when LJ01 is not null then LJ01 else '0' end ) as LJ01 
,(case when LJ02 is not null then LJ02 else '0' end ) as LJ02
,(case when LJ03 is not null then LJ03 else '0' end ) as LJ03
,(case when LJ04 is not null then LJ04 else '0' end ) as LJ04 
,(case when LJ05 is not null then LJ05 else '0' end ) as LJ05
,(case when LJ06 is not null then LJ06 else '0' end ) as LJ06
,(case when LJ07 is not null then LJ07 else '0' end ) as LJ07
,(case when LJ08 is not null then LJ08 else '0' end ) as LJ08 
,(case when LJ09 is not null then LJ09 else '0' end ) as LJ09 
,(case when LJ10 is not null then LJ10 else '0' end ) as LJ10
,(case when LJ11 is not null then LJ11 else '0' end ) as LJ11
,(case when LJ12 is not null then LJ12 else '0' end ) as LJ12 

FROM #YT
LEFT JOIN 
(
--本日收款
select A4,sum(A5) as BDLJ
from #SKSUM 
where convert(char(10), A1, 120) = @BDate
group by A4
) as BDLJ
 ON #YT.A4 = BDLJ.A4
--后面就是重复

union all

--支出表

union all
--总收支

最后，由于采用太多临时表，
在金蝶K3 BOS平台上不能写删除语句，反复运行过多时会报错，致电金蝶说建议采用存储过程写。于是目前在研究中。