DENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER function slot (@intime datetime ,@outtime datetime,@slot char(100),@maxslot varchar(100))
returns decimal(20,1)
as
begin
declare @nday int,@nhour int ,@nmin int ,@alltime int,@din int,@dout int ,@time int
declare @allmoney money,@allmoneyA money,@allmoneyB money,@allmoneyC money,@allmoneyD money,@allmoneyE money
declare @a int,@b int ,@c int,@d int,@e int,@f int
select @alltime = datediff(minute,@intime,@outtime)
select @maxslot=isnull(@maxslot,'000000')
select @allmoney = 0
select @nday = @alltime/60/24 --天数
select @nhour = (@alltime-@nday*24*60)/24 --小时
select @nmin = @alltime-@nday*24*60-@nhour*60+1 --分钟
select @a = 0 ,@b =convert(int,substring(@slot,3,2)), @c =convert(int,substring(@slot,5,2)),@d=convert(int,substring(@slot,3,2))+24,
@e =convert(int,substring(@slot,5,2))+24, @f=24+24
-- select @a = 0,@b=17,@c=20,@d=24+17,@e=24+20,@f=24+24
--对两天时间内的设置段点
if
@alltime<=convert(int,substring(@slot,1,2))
return @allmoney
begin
if (select datepart(hour, @intime))>=@a
set @din=(select datepart(hour, @intime))
else
set @din=@a
if (select datepart(hour, @outtime))<@b
set @dout=(select datepart(hour, @outtime))
else set @dout=@b
end
begin
if (@dout-@din)<0
set @allmoney=0
else
if (@dout-@din)<=convert(int,substring(@slot,14,1))
set @allmoneyA=convert(int,substring(@slot,15,2))
if @allmoneyA>=convert(int,substring(@maxslot,4,3))
set @allmoneyA=convert(int,substring(@maxslot,4,3))
else
set @allmoneyA=(@dout-@din)*convert(int,substring(@slot,12,2))
end
--A-B段
begin
if (select datepart(hour, @intime))>=@b
set @din=(select datepart(hour, @intime))
else
set @din=@b
if (select datepart(hour, @outtime))<@c
set @dout=(select datepart(hour, @outtime))
else set @dout=@c
end
begin
if (@dout-@din)<=convert(int,substring(@slot,9,1))
set @allmoneyB=convert(int,substring(@slot,10,2))
if @allmoneyB>=convert(int,substring(@maxslot,1,3))
set @allmoneyB=convert(int,substring(@maxslot,1,3))
else
set @allmoneyB=(@dout-@din)*convert(int,substring(@slot,7,2))
end
--B-C段
begin
if (select datepart(hour, @intime))>=@c
set @din=(select datepart(hour, @intime))
else
set @din=@c
if (select datepart(hour, @outtime))+24<@d
set @dout=(select datepart(hour, @outtime))+24
else set @dout=@d
end
begin
if (@dout-@din)<=convert(int,substring(@slot,14,1))
set @allmoneyC=convert(int,substring(@slot,15,2))
if @allmoneyC>=convert(int,substring(@maxslot,4,3))
set @allmoneyC=convert(int,substring(@maxslot,4,3))
else
set @allmoneyC=(@dout-@din)*convert(int,substring(@slot,12,2))
end
--C-D段
begin
if (select datepart(hour, @intime))+24>=@d
set @din=(select datepart(hour, @intime))+24
else
set @din=@d
if (select datepart(hour, @outtime))+24<@e
set @dout=(select datepart(hour, @outtime))+24
else set @dout=@e
end
begin
if (@dout-@din)<=convert(int,substring(@slot,9,1))
set @allmoneyD=convert(int,substring(@slot,10,2))
if @allmoneyD>=convert(int,substring(@maxslot,1,3))
set @allmoneyD=convert(int,substring(@maxslot,1,3))
else
set @allmoneyD=(@dout-@din)*convert(int,substring(@slot,7,2))
end
--D-E段
begin
if (select datepart(hour, @intime))+24>=@e
set @din=(select datepart(hour, @intime))+24
else
set @din=@e
if (select datepart(hour, @outtime))+24<@f
set @dout=(select datepart(hour, @outtime))+24
else set @dout=@f
end
begin
if (@dout-@din)<=convert(int,substring(@slot,14,1))
set @allmoneyE=convert(int,substring(@slot,15,2))
if @allmoneyE>=convert(int,substring(@maxslot,4,3))
set @allmoneyE=convert(int,substring(@maxslot,4,3))
else
set @allmoneyE=(@dout-@din)*convert(int,substring(@slot,12,2))
end
--E-F段
set @allmoney=@allmoneyA+@allmoneyB+@allmoneyC+@allmoneyD+@allmoneyE
if @allmoney>=convert(int,substring(@maxslot,7,3))
set @allmoney=convert(int,substring(@maxslot,7,3))
else
set @allmoney=@allmoneyA+@allmoneyB+@allmoneyC+@allmoneyD+@allmoneyE
return @allmoney
end
go
select dbo.slot('2012-8-8 1:00','2012-8-8 4:00','1010202023010220','200100300')
1010202023010220
免费10分钟 10点到20点 每小时20元 2小时内 20元 其余时间每小时收费 10元 2小时内 20元
200100300
10-20点最后200元 其余时间最多100元 一天最高300元
最后是用一个while循环 设置两天内的5个时间段
0-10,10-20 ,20-34,34-44,44-48
然后算出大于1天和大于多天的情况
上面的程序在调试 上不正确 可通过 钱数不对