IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetQtyLodgForMonth'))) THEN
Drop function GetQtyLodgForMonth
END IF
GO

create function GetQtyLodgForMonth(in @RegNo integer,in @NumberLodgings integer)
returns integer
begin
  declare @DateEnd date;
  declare @DateBegin date;
  declare @res integer;
  
  select DateRegDt, dateadd(day,@NumberLodgings,DateRegDt) into @DateBegin,@DateEnd from Book where RegNum=@RegNo;
  
  if (@DateEnd > dateadd(month,1,date('1.'||bvrSpravkaM||'.'||bvrSpravkaY))) then
    set @DateEnd = dateadd(month,1,date('1.'||bvrSpravkaM||'.'||bvrSpravkaY));
  end if;
  if (@DateBegin < date('1.'||bvrSpravkaM||'.'||bvrSpravkaY)) then
    set @DateBegin = date('1.'||bvrSpravkaM||'.'||bvrSpravkaY);
  end if;
  set @res = datediff(day,@DateBegin,@DateEnd);
  if (@res < 0) then 
    set @res = 0;
  end if;
  return (@res)
end

go