一个极度复杂的递归问题,高手请进....................................
财务方面的成本结算方法(加权平均法)
有一个表 #t 如下:
产品id 产品名称 期初库存 单价 日期
1 aa 100 5 2007-1-1
有一个表 #p 如下:
产品id 产品名称 购进数量 单价 日期
1 aa 10 5 2007-1-2
1 aa 20 6 2007-1-10
1 aa 5 7 2007-2-10
1 aa 10 6 2007-2-20
有一个表 #m 如下:
产品id 产品名称 销售数量 单价 日期
1 aa 5 8 2007-1-3
1 aa 10 8 2007-1-11
1 aa 10 9 2007-2-5
1 aa 5 8 2007-2-22
1月份的销售成本=(5+10)*(100*5+10*5+20*6)/(100+10+20)=15*5.15
1月末的库存数量=100+10+20-5-10=115
1月末的库存单价=(100*5+10*5+20*6)/(100+10+20)=5.15
2月份的销售成本=(10+5)*(115*5.15+5*7+10*6)/(115+5+10)=15*5.28
2月末的库存数量=115+5+10-10-5=115
2月末的库存单价=(115*5.15+5*7+10*6)/(100+5+10)=5.28
依次类推
3月末的库存单价=(2月末库存单价*2月末库存数量+3月购进数量*3月购进单价)/
( 2月末库存数量+3月购进数量)
3月末的库存数量=2月末库存数量+3月购进数量-3月销售数量
3月份的销售成本=3月销售数量*3月末的库存单价
请问世这样的期末库存,库存单价,销售成本用什么方法得到
[解决办法]
可以做过存储过程
建议先建立一个临时表，保存上个月计算的结果，
create table #t(产品id int, 产品名称 nvarchar(10), 期初库存 int , 单价 numeric(10,4) , 日期 datetime)
insert into #t select 1 , 'aa ', 100 , 5 , '2007-1-1 '
create table #p (产品id int , 产品名称 nvarchar(10), 购进数量 int , 单价 numeric(10,4) , 日期 datetime)
insert into #p select 1 , 'aa ' , 10 , 5 , '2007-1-2 '
union select 1 , 'aa ' , 20 , 6 , '2007-1-10 '
union select 1 , 'aa ' , 5 , 7 , '2007-2-10 '
union select 1 , 'aa ' , 10 , 6 , '2007-2-20 '
create table #m (产品id int , 产品名称 nvarchar(10), 销售数量 int , 单价 numeric(10,4) , 日期 datetime)
insert into #m select 1 , 'aa ' , 5 , 8 , '2007-1-3 '
union select 1 , 'aa ' , 10 , 8 , '2007-1-11 '
union select 1 , 'aa ' , 10 , 9 , '2007-2-5 '
union select 1 , 'aa ' , 5 , 8 , '2007-2-22 '
--create proc JiaQuan
create table #a (产品id int ,产品名称 nvarchar(10),库存数量 int ,库存单价 numeric(10,4),销售成本 numeric(10,4),month int ,year int )
insert into #a
select distinct a.产品id,a.产品名称,期初库存+购进数量-销售数量 as 库存数量, (期初库存*a.单价+b.单价)/(期初库存+购进数量) as 库存单价,
销售数量* (期初库存*a.单价+b.单价)/(期初库存+购进数量) as 销售成本,month(a.日期),year(a.日期)
from #t a inner join (select 产品id, sum(购进数量) as 购进数量,sum(购进数量*单价) as 单价 from #p where month(日期)=1 and year(日期)=2007 group by 产品id ) b on a.产品id=b.产品id
inner join (select 产品id, sum(销售数量) as 销售数量 from #m where month(日期)=1 and year(日期)=2007 group by 产品id ) c on a.产品id=c.产品id
where month(a.日期) =1
insert into #a
select distinct a.产品id,a.产品名称,a.库存数量+购进数量-销售数量 as 库存数量, (a.库存数量*a.库存单价+b.单价)/(a.库存数量+购进数量) as 库存单价,
销售数量* (a.库存数量*a.库存单价+b.单价)/(a.库存数量+购进数量) as 销售成本,2,year
from #a a inner join (select 产品id, sum(购进数量) as 购进数量,sum(购进数量*单价) as 单价 from #p where month(日期)=2 and year(日期)=2007 group by 产品id ) b on a.产品id=b.产品id
inner join (select 产品id, sum(销售数量) as 销售数量 from #m where month(日期)=2 and year(日期)=2007 group by 产品id ) c on a.产品id=c.产品id
where month =1
insert into #a
select distinct a.产品id,a.产品名称,a.库存数量+购进数量-销售数量 as 库存数量, (a.库存数量*a.库存单价+b.单价)/(a.库存数量+购进数量) as 库存单价,
销售数量* (a.库存数量*a.库存单价+b.单价)/(a.库存数量+购进数量) as 销售成本,3,year
from #a a inner join (select 产品id, sum(购进数量) as 购进数量,sum(购进数量*单价) as 单价 from #p where month(日期)=3 and year(日期)=2007 group by 产品id ) b on a.产品id=b.产品id
inner join (select 产品id, sum(销售数量) as 销售数量 from #m where month(日期)=3 and year(日期)=2007 group by 产品id ) c on a.产品id=c.产品id
where month =2
select * from #a
lz可以吧month 写成while 条件循环，这样就不用每个月都罗列了
--result
id 名称 库存数量 库存单价 销售成本 month year
1 aa 115 5.1538 77.3077 1 2007
1 aa 115 5.2899 79.3485 2 2007
[解决办法]
create table t
(产品id int, 产品名称 varchar(10), 期初库存 int,单价 int, 日期 datetime)
insert into t select 1, 'aa ', 100, 5, '2007-1-1 '
create table p
(产品id int, 产品名称 varchar(10), 购进数量 int, 单价 int, 日期 datetime)
insert into p select 1, 'aa ', 10, 5, '2007-1-2 '
union all select 1, 'aa ', 20, 6, '2007-1-10 '
union all select 1, 'aa ', 5, 7, '2007-2-10 '
union all select 1, 'aa ', 10, 6, '2007-2-20 '
create table m
(产品id int, 产品名称 varchar(10),销售数量 int,单价 int, 日期 datetime)
insert into m select 1, 'aa ', 5, 8, '2007-1-3 '
union all select 1, 'aa ', 10, 8, '2007-1-11 '
union all select 1, 'aa ', 10, 9, '2007-2-5 '
union all select 1, 'aa ', 5, 8, '2007-2-22 '
alter FUNCTION F_ww (@DATE varchar(7))
RETURNS varchar(100)
AS
BEGIN
declare @value dec(18,4),@stock int,@valueup dec(18,2),@stockup int,@int int
select @stockup=期初库存,@valueup=单价 from t
select
@int=(isnull(b.销售数量,0)*(@stockup*@valueup+isnull(a.购进成本,0))/(@stockup+isnull(a.购进数量,0))),
@stock=(@stockup+isnull(a.购进数量,0)-isnull(b.销售数量,0)),
@value=(@stockup*@valueup+isnull(a.购进成本,0))/(@stockup+isnull(a.购进数量,0)),
@valueup=(@stockup*@valueup+isnull(a.购进成本,0))/(@stockup+isnull(a.购进数量,0)),
@stockup=(@stockup+isnull(a.购进数量,0)-isnull(b.销售数量,0))
from(select convert(varchar(7),日期,120)[日期],sum(购进数量)[购进数量],sum(购进数量*单价)[购进成本]
from p group by convert(varchar(7),日期,120))a
full join
(select convert(varchar(7),日期,120)[日期],sum(销售数量)[销售数量]
from m group by convert(varchar(7),日期,120))b
on b.日期=a.日期
where a.日期 <=@DATE
order by isnull(a.日期,b.日期)
RETURN( '本月库存: '+cast(@stock as varchar(10))+ ' 销售成本: '+cast(@int as varchar(10))+ ' 库存单价: '+cast(@value as varchar(10)))
END
select a.*,dbo.f_ww(isnull(a.日期,b.日期))[汇总]
from(select convert(varchar(7),日期,120)[日期]
from #p group by convert(varchar(7),日期,120))a
full join
(select convert(varchar(7),日期,120)[日期]
from #m group by convert(varchar(7),日期,120))b
on b.日期=a.日期
order by isnull(a.日期,b.日期)
日期 汇总
------- ---------------------
2007-01 本月库存:115 销售成本:77 库存单价:5.1538
2007-02 本月库存:115 销售成本:79 库存单价:5.2865
