请帮忙把下面的存储过程改写成一个用户自定义函数输出,谢谢!
create table Cost(car char(2),Type varchar(20),cost money,
starttime datetime ,endtime datetime )
insert into cost
select 'A_ ' , '管理费 ' ,100, '2007-05-29 ', '2007-06-08 '
union all select 'A_ ' , '保险费 ' ,1000, '2007-06-01 ', '2008-06-01 '
union all select 'B_ ' , '管理费 ' ,300, '2007-04-22 ', '2007-08-22 '
create table sjwork(sjname char(8),car char(2),riqi datetime)
insert into sjwork --truncate table sjwork
select 'A ' , 'A_ ' , '2007-06-01 '
union all select 'A ', 'B_ ', '2007-06-05 '
union all select 'A ', 'A_ ', '2007-06-12 '
union all select 'B ', 'B_ ' , '2007-06-01 '
union all select 'B ', 'A_ ' , '2007-06-05 '
union all select 'B ' , 'B_ ' , '2007-06-12 '
union all select 'C ' , 'C_ ', '2007-05-22 '
create table sj
(sjname char(8) not null)
insert into sj
select 'A '
union all select 'B '
union all select 'C '
----------------------------------------------------///
create alter proc carcost @starttime datetime,@endtime datetime,@sjname char(8),@cost money output as
begin
select car,type,
(case when @starttime > =starttime then @starttime else starttime end)新开始,
(case when @endtime <=endtime then @endtime else endtime end)新结束,
cost*datediff(d,(case when @starttime > =starttime then @starttime else starttime end),
dateadd(d,1,(case when @endtime <=endtime then @endtime else endtime end)))/
datediff(d,starttime,dateadd(d,1,endtime))新费用 into #cost from cost
where not (@endtime <starttime or endtime <@starttime)
select a.*,isnull((select top 1 dateadd(d,-1,riqi) from sjwork b
where a.sjname=b.sjname and a.riqi <b.riqi
order by datediff(d,a.riqi,b.riqi)),@endtime) riqi2
into #sjwork from sjwork a where a.sjname=@sjname
--select * from #cost
--select * from #sjwork
select @cost=sum(最终费用) from(
select type,新费用*datediff(d,(case when riqi > =新开始 then riqi else 新开始 end),
dateadd(d,1,(case when riqi2 <=新结束 then riqi2 else 新结束 end))
)/datediff(d,新开始,dateadd(d,1,新结束))最终费用
from #cost a left join #sjwork b on a.car=b.car
where not (riqi > 新结束 or riqi2 <新开始)
)a
drop table #cost
drop table #sjwork
end
------------------------------------------------------///
下面是执行验证的语句
declare @a money
exec carcost '2007-06-01 ', '2007-06-22 ', 'A ',@a output
select 'A ' 司机名,@a 费用总和
[解决办法]
create function fn_carcost (@starttime datetime,@endtime datetime,@sjname char(8))
returns money
as
begin
declare @cost money
select car,type,
(case when @starttime > =starttime then @starttime else starttime end)新开始,
(case when @endtime <=endtime then @endtime else endtime end)新结束,
cost*datediff(d,(case when @starttime > =starttime then @starttime else starttime end),
dateadd(d,1,(case when @endtime <=endtime then @endtime else endtime end)))/
datediff(d,starttime,dateadd(d,1,endtime))新费用 into #cost from cost
where not (@endtime <starttime or endtime <@starttime)
select a.*,isnull((select top 1 dateadd(d,-1,riqi) from sjwork b
where a.sjname=b.sjname and a.riqi <b.riqi
order by datediff(d,a.riqi,b.riqi)),@endtime) riqi2
into #sjwork from sjwork a where a.sjname=@sjname
--select * from #cost
--select * from #sjwork
select @cost=sum(最终费用) from(
select type,新费用*datediff(d,(case when riqi > =新开始 then riqi else 新开始 end),
dateadd(d,1,(case when riqi2 <=新结束 then riqi2 else 新结束 end))
)/datediff(d,新开始,dateadd(d,1,新结束))最终费用
from #cost a left join #sjwork b on a.car=b.car
where not (riqi > 新结束 or riqi2 <新开始)
)a
return @cost
drop table #cost
drop table #sjwork
end
