财务费用表ACTTB
TT001(年度),TT002(月份),TB005(会计科目),TB006(部门),TB007(金额)
2013 01 660202 30101 200
2013 02 660202 30101 5002013 01 660208 30101 200
2013 02 660208 30101 100
2013 03 660208 30101 200
2013 04 660208 30101 2002013 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(截止当月发生的费用)的实现 谢谢
TT001(年度),TT002(月份),TB005(会计科目),TB006(部门),TB007(金额)
2013 01 660202 30101 200
2013 02 660202 30101 5002013 01 660208 30101 200
2013 02 660208 30101 100
2013 03 660208 30101 200
2013 04 660208 30101 2002013 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,500declare @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)
*/
用年和月做条件就可以查询出来.
这个是在ERP里面做一个报表,不知道如何使用这些变量
谢谢
(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
*/