求助折旧算法的SQL

Question

举例：A表存放固定资产记录 主要字段 ID,原始价格price
     B表存放折旧记录主要字段ID,旧折旧率old_depre，新折旧率new_depre，生效日期eff_date折旧率要调整，而且调整不止一次，所以B表是多条记录，折旧率是按月计算，比如
1月份 原始价格10块，折旧率0.02
5月份，原始价格10， 折旧率0.01
7月份 原始价格10，折旧率0.005
结果是如下(折旧1号生效)1月,10-10*0.02 = 9.8
2月, 10 - 10*0.02*2 = 9.6
3月,10 - 10*0.02*3 = 9.4
4月,10 - 10*0.02*4 = 9.25月份用新的折旧率 9.2-10*0.01 = 9.1
6月,9.1-10*0.01 = 9.0
7月份用新的折旧率 9.0 - 10*0.005 = 8.95
.......
结果集是  ID,原始价格,第几月，折旧率,还剩净值
           1   10      1      0.02  9.8
          .......
          1   10      7     0.005  8.95哪位高手指点一二？？

Answer 1

use tempdb
go
create table 商品价格表(商品id int ,价格 int)
create table 商品打折表(商品id int ,旧打折率 float,新打折率  float ,月份 datetime)
go
insert 商品价格表
select 1,10
union all select 2,20
go
insert 商品打折表
select 1,0,0.02,'2009-01-01'
union all select 1,0.02,0.01,'2009-04-01'
union all select 1,0.01,0.03,'2009-07-01'
union all select 1,0.03,0.05,'2009-09-01'
union all select 2,0,0.01,'2009-01-01'
union all select 2,0.01,0.05,'2009-02-01'
union all select 2,0.05,0.2,'2009-12-01'
create proc yd_test
as
begin 
-- temp1:
select a.商品id, a.价格, (datepart(mm,b.月份)) as 月份,b.旧打折率,b.新打折率
into temp1
from  商品价格表 a join 商品打折表 b on a.商品id  =b.商品id
----temp2
select rowid = row_number() over(partition by 商品id order by 价格 asc ),a.*
into temp2
from  temp1 as a--temp3
create table temp3(row_id int identity(1,1),商品id int,月份 int,折旧率 float ,价格 float,折后价 float)declare @num1 int ,@num2 int 
select @num1 = (select count(distinct 商品id) from temp2)
set @num2 = 1
while @num2<=@num1 and @num1 ! = 0
begin
declare @num3 int ,@num4 int 
select  @num3 = (select max(月份) from temp2 where 商品id  = @num2)
set @num4 = 1
while @num4<=@num3 and @num3! = 0
begin
insert into temp3(商品id,月份) select @num2  , @num4 
set @num4 = @num4+1
end 
 set @num2 =@num2+1
end --temp4
select a.row_id,a.商品id, 
(select 价格 from temp2 c where c.商品id  = a.商品id  and  rowid in (select min (rowid) from temp2 where 商品id=c.商品id  ) )as 价格,
a.月份,b.旧打折率,b.新打折率,a.折旧率,a.折后价
into temp4
from temp3 a left outer join temp2 b on a.商品id  = b.商品id and a.月份 = b.月份 declare @num5 int ,@num6 int
select @num5= (select max(row_id) from temp4)
set @num6 =1
while @num6<=@num5
begin 
update a
set 折旧率 = case when   ((a.旧打折率 != a.新打折率) and (a.折旧率 is null))  then a.新打折率         
  when   nullif(a.旧打折率 ,0 ) is null then (select 折旧率 from temp4 where row_id = a.row_id-1)
  else 0
 end
from temp4 a
where a.row_id =@num6
set @num6= @num6+1
end declare @num7 int ,@num8 int
select @num7= (select max(row_id) from temp4)
set @num8 =1
while @num8<=@num7
begin 
update a
set 
折后价 = case when (a.旧打折率 != a.新打折率)  and a.月份 =1   then a.价格*(1-a.折旧率)
      when (nullif(a.旧打折率 ,0 ) is null) then a.价格*(1-a.折旧率*a.月份)
  when (a.旧打折率 != a.新打折率) and (a.月份 !=1) then (select 折后价 from temp4 where row_id = a.row_id - 1) *(1-a.折旧率)
end
from temp4 a
where a.row_id =@num8set @num8 = @num8+1
end--temp5
select a.row_id ,a.商品id,a.价格,a.月份,a.折旧率,a.折后价 
into temp5 
from   temp4 aselect * from temp5
end 
---
drop table temp1,temp2,temp3,temp4,temp5
drop proc yd_test
exec   yd_test--
1 1 10 1 0.02 9.8
2 1 10 2 0.02 9.6
3 1 10 3 0.02 9.4
4 1 10 4 0.01 9.306
5 1 10 5 0.01 9.5
6 1 10 6 0.01 9.4
7 1 10 7 0.03 9.118
8 1 10 8 0.03 7.6
9 1 10 9 0.05 7.22
10 2 20 1 0.01 19.8
11 2 20 2 0.05 18.81
12 2 20 3 0.05 17
13 2 20 4 0.05 16
14 2 20 5 0.05 15
15 2 20 6 0.05 14
16 2 20 7 0.05 13
17 2 20 8 0.05 12
18 2 20 9 0.05 11
19 2 20 10 0.05 10
20 2 20 11 0.05 9
21 2 20 12 0.2 7.2