Declare @T Table(
CFF_Loanno nvarchar(100),
BANK_Name_2 nvarchar(100),
CFF_code nvarchar(100),
CFF_date datetime,
CFF_amt decimal(18, 6),
CFF_repay_date datetime,
COMP_Name_2 nvarchar(100)
)
---临时表，存放基础数据
insert into @T(CFF_Loanno ,
BANK_Name_2 ,
CFF_code,
CFF_date,
CFF_amt,
CFF_repay_date,
COMP_Name_2)
SELECT A.CFF_Loanno,B.BANK_Name_2,A.CFF_code,A.CFF_date,A.CFF_amt,A.CFF_repay_date,C.COMP_Name_2
from cashflow_financing as A
inner join Bank as B on
A.CFF_Bank = B.BANK_Code
inner join Company as C on
A.CFF_repay_company = C.COMP_Code
---小计表，按照还款日期汇总
select N'B' as ord,N'小计' as class,
CFF_Loanno as CFF_Loanno ,
CONVERT(varchar(100), CFF_repay_date, 23) as BANK_Name_2 ,
NULL as CFF_code,
NULL as CFF_date,
isnull((select sum(CFF_amt) from @T as a where a.CFF_Loanno = b.CFF_Loanno and a.CFF_repay_date = b.CFF_repay_date), 0) as CFF_amt,
NULL as CFF_repay_date,
NULL as COMP_Name_2
from @T as b group by CFF_Loanno, CFF_repay_date
union all
---明细表
select N'A' as ord,N'明细' as class,c.*
from @T as c
union all
---合计表
select N'C' as ord,N'合计' as class,
CFF_Loanno as CFF_Loanno ,
NULL as BANK_Name_2 ,
NULL as CFF_code,
NULL as CFF_date,
isnull((select sum(CFF_amt) from @T as d where d.CFF_Loanno = e.CFF_Loanno), 0) as CFF_amt,
NULL as CFF_repay_date,
NULL as COMP_Name_2
from @T as e
