نمی خواستم با cursor بنویسم ولی هیچ راه حلی ندادین اینم پروسیژر
Create function dbo.CalcPrice (@Count float)
RETURNS
numeric(18,0)
as
begin
declare @from float,@to float , @price numeric(18,0),@Out numeric(18,0),@to_befor float
set @out=0
set @to_befor=0
DECLARE cur1 CURSOR FOR
SELECT [from], [to],price FROM Tb1
OPEN cur1
FETCH NEXT FROM cur1 into @from,@to,@price
WHILE @@FETCH_STATUS = 0
BEGIN
if(@Count>=@to )
set @Out=@Out+ ((@to-@from+1)*@price)
else
if (@Count>=@from)
set @Out=@Out+ ((@Count-@from+1)*@price)
FETCH NEXT FROM cur1 into @from,@to,@price
END
CLOSE cur1
DEALLOCATE cur1
return round(@out,0)
end