--declare @recid varchar(15) , @erechbuza datetime, @schumRibitPigurim decimal (18,2) , @HovRibitPigurimToday decimal (18,2)

--set @recid= (select min(recid) from contact1 )

--while isnull(@recid,'')<> ''
--begin

--set  @erechbuza= (select erechbuza from #PigurTashlum where recid=@recid)
--set  @schumRibitPigurim = (select schumRibitPigurim from #PigurTashlum where recid=@recid)


--update gp_gemel..HovotMasikim
--set HovRibitPigurimToday= select [dbo].[sp_u_Return_HovRibitPigurimToday] (@erechbuza,@schumribitpigurim)
--where recid= @recid

--set @recid= (select min(recid) from #pigurtashlum p where p.recid > @recid)
--end

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

create FUNCTION [dbo].[sp_u_Return_HovRibitPigurimToday]
(@ErechBuza datetime ,
 @schumRibitPigurim decimal (18,2))
 returns decimal(18,2)
as

BEGIN
declare @FinishDate datetime = (select dateadd(day,-1,max(Ribit_Date)) from RibitHashkal ) --תמיד יחזיר יום לפני אחרון בהנחה ומעודכן עד היום

while @ErechBuza <= @FinishDate
begin

   declare @HovRibitPigurimToday decimal(18,2)
   declare @Daily_Ribit decimal(18,2) = ( select Ribit from RibitHashkal rh where rh.Ribit_Date = @ErechBuza)


   set @HovRibitPigurimToday= @schumRibitPigurim * ( 1 + @Daily_Ribit/100/365) ---עדכון חוב התחלתי
   set @ErechBuza = dateadd(day,1,@ErechBuza)
   set @schumRibitPigurim=@HovRibitPigurimToday
end


return @HovRibitPigurimToday
END

--create  table RibitHashkal  (Ribit_Date datetime ,Ribit decimal(18,2) )
--insert into RibitHashkal values ('2018-06-25 00:00:00.000','50.00')
--select* from RibitHashkal

--select [dbo].[sp_u_Return_HovRibitPigurimToday] ('2018-06-23 00:00:00.000','600.00')