求累计金额,万分感谢
财务费用表ACTTB
TT001(年度),TT002(月份),TB005(会计科目),TB006(部门),TB007(金额)
2013 01 660202 30101 200
2013 02 660202 30101 500
2013 01 660208 30101 200
2013 02 660208 30101 100
2013 03 660208 30101 200
2013 04 660208 30101 200
2013 03 660207 30101 100
2013 04 660207 30101 500
比如现在要查询4月的费用,求如何实现
TT001(年度),TT002(月份),TB005(会计科目),TB006(部门),TB007(金额),BB(截止4月的累积)
2013 04 660202 30101 0 200+500
2013 04 660208 30101 200 200+100+200+200
2013 04 660207 30101 500 100+500
要查3月的费用显示为
TT001(年度),TT002(月份),TB005(会计科目),TB006(部门),TB007(金额),BB(截止3月的累积)
2013 03 660202 30101 0 200+500
2013 03 660208 30101 200 200+100+200
2013 03 660207 30101 100 100
主要是TB007(当月发生费用)和BB(截止当月发生的费用)的实现 谢谢
[解决办法]
--> 测试数据：@ACTTB
declare @ACTTB table([TT001] int,[TT002] varchar(2),[TB005] int,[TB006] int,[TB007] int)
insert @ACTTB
select 2013,'01',660202,30101,200 union all
select 2013,'02',660202,30101,500 union all
select 2013,'01',660208,30101,200 union all
select 2013,'02',660208,30101,100 union all
select 2013,'03',660208,30101,200 union all
select 2013,'04',660208,30101,200 union all
select 2013,'03',660207,30101,100 union all
select 2013,'04',660207,30101,500
declare @month int set @month=4 --这个位置可以改成3
select
[TT001],@month as TT002,[TB005],sum([TB007]) as TB007
from @ACTTB
where [TT002]+0<=@month
group by [TT001],[TB005]
[解决办法]
create table ACTTB
(TT001 varchar(10), TT002 varchar(10), TB005 varchar(10), TB006 varchar(10), TB007 int)
insert into ACTTB
select '2013', '01', '660202', '30101', 200 union all
select '2013', '02', '660202', '30101', 500 union all
select '2013', '01', '660208', '30101', 200 union all
select '2013', '02', '660208', '30101', 100 union all
select '2013', '03', '660208', '30101', 200 union all
select '2013', '04', '660208', '30101', 200 union all
select '2013', '03', '660207', '30101', 100 union all
select '2013', '04', '660207', '30101', 500
declare @tt002 varchar(10)
select @tt002='04'
select TT001,
@tt002 'TT002',
TB005,
TB006,
sum(case when TT002=@tt002 then TB007 else 0 end) 'TB007',
sum(TB007) 'BB'
from ACTTB
where TT002<=@tt002
group by TT001,TB005,TB006
/*
TT001 TT002 TB005 TB006 TB007 BB
---------- ---------- ---------- ---------- ----------- -----------
2013 04 660202 30101 0 700
2013 04 660207 30101 500 600
2013 04 660208 30101 200 700
(3 row(s) affected)
*/
declare @tt002 varchar(10)
select @tt002='03'
select TT001,
@tt002 'TT002',
TB005,
TB006,
sum(case when TT002=@tt002 then TB007 else 0 end) 'TB007',
sum(TB007) 'BB'
from ACTTB
where TT002<=@tt002
group by TT001,TB005,TB006
/*
TT001 TT002 TB005 TB006 TB007 BB
---------- ---------- ---------- ---------- ----------- -----------
2013 03 660202 30101 0 700
2013 03 660207 30101 100 100
2013 03 660208 30101 200 500
(3 row(s) affected)
*/
[解决办法]
用存储过程实现,前端程序访问存储过程,输入参数为月份,返回结果存为Recordset..
create table ACTTB
(TT001 varchar(10), TT002 varchar(10), TB005 varchar(10), TB006 varchar(10), TB007 int)
insert into ACTTB
select '2013', '01', '660202', '30101', 200 union all
select '2013', '02', '660202', '30101', 500 union all
select '2013', '01', '660208', '30101', 200 union all
select '2013', '02', '660208', '30101', 100 union all
select '2013', '03', '660208', '30101', 200 union all
select '2013', '04', '660208', '30101', 200 union all
select '2013', '03', '660207', '30101', 100 union all
select '2013', '04', '660207', '30101', 500
-- 创建存储过程
create proc sperp001
(@tt002 varchar(10))
as
begin
set nocount on
select TT001,
@tt002 'TT002',
TB005,
TB006,
sum(case when TT002=@tt002 then TB007 else 0 end) 'TB007',
sum(TB007) 'BB'
from ACTTB
where TT002<=@tt002
group by TT001,TB005,TB006
end
go
-- 查询4月费用
exec sperp001 '04'
/*
TT001 TT002 TB005 TB006 TB007 BB
---------- ---------- ---------- ---------- ----------- -----------
2013 04 660202 30101 0 700
2013 04 660207 30101 500 600
2013 04 660208 30101 200 700
*/
-- 查询3月费用
exec sperp001 '03'
/*
TT001 TT002 TB005 TB006 TB007 BB
---------- ---------- ---------- ---------- ----------- -----------
2013 03 660202 30101 0 700
2013 03 660207 30101 100 100
2013 03 660208 30101 200 500
*/
[解决办法]
OK