select T1.日期,T1.银行名称,T1.银行账号,T1.客户代码,T1.客户名称,T1.往来客户单位,
case
when 收付类型='收款' Then
T1.金额
else
0
End
as 收款金额,case
when 收付类型='付款' Then
T1.金额
else
0
End
as 付款金额into #Data4
from #Data3 T1
select * into #Data5 From
(select '收付' as 类型,*,sum(收款金额)-sum(付款金额) as 结余,0 as 转账 from #Data4 group by ?)
case
when 收付类型='收款' Then
T1.金额
else
0
End
as 收款金额,case
when 收付类型='付款' Then
T1.金额
else
0
End
as 付款金额into #Data4
from #Data3 T1
select * into #Data5 From
(select '收付' as 类型,*,sum(收款金额)-sum(付款金额) as 结余,0 as 转账 from #Data4 group by ?)
Server: Msg 8120, Level 16, State 1, Line 85
列 '#Data4.收款金额' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
Server: Msg 8120, Level 16, State 1, Line 85
列 '#Data4.付款金额' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
Server: Msg 8120, Level 16, State 1, Line 85
列 '#Data4.收款金额' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
Server: Msg 8120, Level 16, State 1, Line 85
列 '#Data4.付款金额' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
2. select '收付' as 类型,*,sum(收款金额)-sum(付款金额) as 结余,0 as 转账 from #Data4 group by ?)
应该group by 类型
列名 '类型' 无效。
select * into #Data5 From
(select '收付' as 类型,*,(select sum(收款金额-付款金额) from #data4 )as 结余,0 as 转账 from #Data4 )
set nocount ON
---付款
--已经钩稽
select * into #Data3 from (
select T2.fdate as 日期,T3.FName as 银行名称,T3.F_101 as 银行账号,T4.FNumber as 客户代码 ,T4.FName as 客户名称,T5.FName as 往来客户单位,FAmount1 as 金额, '付款' as 收付类型 from t_BOS200000020 T1
Left Join t_BOS200000020Entry2 T2 on t1.FID=T2.FID
Left Join t_Item_3012 T3 on T3.F_101=T1.Ftext3
Left Join T_Organization T4 on T4.FItemID=T2.FBase
left Join T_Supplier T5 on T5.FItemID=T2.FBase1
where isnull(T2.FText4,'需要统计')='需要统计'--未钩稽
union all
select T2.fdate as 日期,T3.FName as 银行名称,T3.F_101 as 银行账号,T4.FNumber as 客户代码 ,T4.FName as 客户名称,T5.FName as 往来客户单位,FAmount1 as 金额, '付款' as 收付类型
from t_BOS200000021 T1
Left Join t_BOS200000022Entry2 T2 on t1.FID=T2.FID
Left Join t_Item_3012 T3 on T3.F_101=T1.FText1
Left Join T_Organization T4 on T4.FItemID=T2.FBase
left Join T_Supplier T5 on T5.FItemID=T2.FBase1
union all
--收款
--已经钩稽
select T2.fdate as 日期,T3.FName as 银行名称,T3.F_101 as 银行账号,T4.FNumber as 客户代码 ,T4.FName as 客户名称,T5.FName as 往来客户单位,FAmount1 as 金额, '收款' as 收付类型
from t_BOS200000022 T1
Left Join t_BOS200000022Entry2 T2 on t1.FID=T2.FID
Left Join t_Item_3012 T3 on T3.F_101=T1.FText3
Left Join T_Organization T4 on T4.FItemID=T2.FBase
left Join T_item_3007 T5 on T5.FItemID=T2.FBase1
where isnull(T2.FText4,'需要统计')='需要统计'
union all--未钩稽
select T2.fdate as 日期,T3.FName as 银行名称,T3.F_101 as 银行账号,T4.FNumber as 客户代码 ,T4.FName as 客户名称,T5.FName as 往来客户单位,FAmount1 as 金额, '收款' as 收付类型
from t_BOS200000023 T1
Left Join t_BOS200000023Entry2 T2 on t1.FID=T2.FID
Left Join t_Item_3012 T3 on T3.F_101=T1.FText1
Left Join T_Organization T4 on T4.FItemID=T2.FBase
left Join T_item_3007 T5 on T5.FItemID=T2.FBase1
) a
--
--
-- -- --
select * into #Data2 from (
select T2.FDate as 日期,T3.FName as 银行名称 ,T3.F_101 as 银行账号,FAmount as 金额 ,T4.FName as 对方银行,'转出' as 类型,null 客户代码, null 客户名称
from t_BOS200000039 T1
Left Join t_BOS200000039Entry2 T2 on T1.FID=T2.FID
left Join t_Item_3012 T3 on T3.FItemID=T2.FBase
Left Join t_Item_3012 T4 on T4.FItemID=T2.FBase1
union allselect T2.FDate as 日期,T3.FName as 银行名称 ,T3.F_101 as 银行账号,FAmount as 金额 ,T4.FName as 对方银行,'转入' as 类型,null 客户代码, null 客户名称
from t_BOS200000039 T1
Left Join t_BOS200000039Entry2 T2 on T1.FID=T2.FID
left Join t_Item_3012 T3 on T3.FItemID=T2.FBase1
Left Join t_Item_3012 T4 on T4.FItemID=T2.FBase
) b
-- --
--
select T1.日期,T1.银行名称,T1.银行账号,T1.客户代码,T1.客户名称,T1.往来客户单位,
case
when 收付类型='收款' Then
T1.金额
else
0
End
as 收款金额,case
when 收付类型='付款' Then
T1.金额
else
0
End
as 付款金额into #Data4
from #Data3 T1--收付明细
select * into #Data5 From (
select '收付' as 类型,*,sum(收款金额)-sum(付款金额) as 结余,0 as 转账 from #Data4
where 日期>='1900-1-1'
AND
日期<='2010-12-8'
AND 银行名称 like '%@YHMC@' AND isnull(客户代码,'') like '@CustNo@%'
group by 类型
union --转账明细
select '转账-收款' 类型,日期,银行名称,null,null,对方银行,null,金额,0,金额-0,0 as 转账 from #Data2
unionselect '转账-付款' 类型,日期,银行名称,null,null,对方银行,null,0,金额,0-sum(金额) as 结余 ,0 as 转账 from #Data2
) cdrop table #Data2,#Data3,#Data4,#Data5这是全部代码,我想把结余合计,group by 应该怎么写?
日期 客户代码 客户名称 往来客户单位 收款金额 付款金额 结余
2010-01-20 501 翔燕 NULL 0 199500.00 91836.50
2010-01-20 501 翔燕 NULL 199500.00 0 91836.50
2010-10-01 501 翔燕 NULL 0 91836.50 91836.50子查询可以的,我要的“结余”是本日期之前的汇总
我需要的结果是日期 客户代码 客户名称 往来客户单位 收款金额 付款金额 结余
2010-01-20 501 翔燕 NULL 0 199500.00 -199500.00
2010-01-20 501 翔燕 NULL 199500.00 0 0
2010-10-01 501 翔燕 NULL 0 91836.50 91836.50
(select sum(收款金额-付款金额) from #data4 group by convert(varchar(10),日期,120) having count(*)=1 )as 结余,