the import take a long time since each time the number of imported rows is about 10000 and the table have many indexes and I have to check the previous rate on each insert ( I can't use the bulk insert).
I have a scenario that I can get the rate without using EndDate
in this case I can remove the EndDate from the table
Do you recommend this way ?
ALTER FUNCTION [dbo].[GetRate] (@prefix varchar(20),@Date datetime,@Tariff_id int)
RETURNS decimal(18,4)
AS
BEGIN
declare @rate decimal(18,4)
select @rate=rate from Tariffs t1 where
prefix=@prefix and
Tariff_id=@Tariff_id and
startDate =
(select max(startDate) from Tariffs t2
where t1.Tariff_id =t2.Tariff_id and t1.prefix=t2.prefix and t2.StartDate <= @Date )
-- code removed -- @Date >= startDate and @Date < dateadd(dd,1,endDate)
order by prefix desc
return @rate
END