IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('CalcConstructionPeriods')))
THEN Drop procedure CalcConstructionPeriods
END IF

GO

create function
DBA.CalcConstructionPeriods(in @RoomNo integer,in @BegDate date,in @EndDate date)
returns text
//--Vryshta periodite ot @BegDate do @EndDate, v koito @RoomNo shte byde v status razlichen ot 'normalna'
//--Naprimer:
//'Remonti : 01.04.2008 - 31.05.2008'
//'------------------------------------'
//   '02.04.2008 - 08.04.2008 (аварийна) - ремонт на баня'
//   '14.04.2008 - 28.04.2008 (нетърговска)'
//   '05.05.2008 - 06.05.2008 (частна)'
begin
  declare @R text;
  declare @d1 date;
  declare @d2 date;
  //
  set bvrData1=@BegDate;
  set bvrData2=@EndDate;
  set @R='';
  set @d1=null;
  set @d2=null;
  for F as Curs scroll cursor for
    select RSFP.StartDate as @Start,RSFP.EndDate as @End,RSFP.Komentar as @Komentar,RS.Description as @Status from RoomStatusForPeriod as RSFP,RoomStatus as RS where
      RSFP.NRoom = @RoomNo and RSFP.NStatus <> 0 and RS.RoomStatusNo = RSFP.NStatus and RSFP.StartDate <= @EndDate and RSFP.EndDate >= @BegDate do
    set @d1=@Start;
    set @d2=@End;
    if @d1 < @BegDate then set @d1=@BegDate
    end if;
    if @d2 > @EndDate then set @d2=@EndDate
    end if;
    if @Komentar <> '' then
      set @R=@R || String(@d1) || ' - ' || String(date(dateadd(day,1,@d2))) || ' (' || @Status || ')' || ' - ' || @Komentar || bvrNEW_LINE
    else set @R=@R || String(@d1) || ' - ' || String(date(dateadd(day,1,@d2))) || ' (' || @Status || ')' || bvrNEW_LINE
    end if;
    if @End >= @d2 then set @d2=@End
    end if end for;
  set @R=bvrNEW_LINE || 'Ремонти : ' || String(@BegDate) || ' - ' || String(@EndDate) || bvrNEW_LINE || '--------------------------------------------------' || bvrNEW_LINE || @R;
  return(@R)
end