CREATE PROCEDURE [dbo].[HotelManagerNumberOfRooms] @ARundate VARCHAR(8), @AYear VARCHAR(4) = '', @AMonth VARCHAR(2) = '', @AHotelNumberStr VARCHAR(100), @ARunType SMALLINT, 
  @ATotal BIT, @ARemaining BIT, @ATotalNumberOfRooms INT = 0 OUTPUT, @AReportOwnerReservation INT, @AInclNUL BIT, @AInclEmpty BIT, 
  @ATotalNumberOfRoomsRooms INT = 0 OUTPUT, @ATotalNumberOfRoomsBeds INT = 0 OUTPUT AS
SET NOCOUNT ON
/*
  Created by Kent Johansen april 2009

  Remarks:
  @AReportOwnerReservation: 
    0: rorExcluded
    1: rorOnly
    2: rorIncluded
    
  @ARuntype:
    None = 0
    RoomsArriving = 1
    RoomsWalkin = 2
    RoomsNoShow = 3
    RoomsDay = 4
    RoomsUpgraded = 5
    RoomsEarlyDeparture = 6
    
  Changes:
    KJ 20180208: Bug: Fix for "Month to date and year to date not showing same numbers". Case #CR141561
    KJ 20171218: Ext: Added Group data by rooms/beds
    KJ 20170629: Bug: Fix for "Wrong number of stays". Case #CR127717
    KJ 20170622: Bug: Fix for "Wrong number of arrival rooms". Case #CR127717
    KJ 20151021: Bug: Fix for "Wrong type of input params"
    KJ 20151012: Bug: Fix for wrong number of no shows. (sometimes followuptype is 5 and sometimes 12. Case #CR093301
    KJ 20130430: Ext: Added incl NUL and Empty products. Req #1530
    KJ 20120430: Ext: Added owner reservations.
    KJ 20100520: Bug: Fix for "Wrong number of dayrooms for month"
    KJ 20090407: Use allocated RoomType if any else use BookedTypeNumber from ReservationRoomLine

*/

SET @AHotelNumberStr = ISNULL(@AHotelNumberStr, '')
SET @AYear = ISNULL(@AYear, '')
SET @AMonth = ISNULL(@AMonth, '')

IF @ARunType = 1
BEGIN
  -- Arrivals
  IF @ATotal = 1
  BEGIN
    SELECT sum(numberofrooms) AS TotalNumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableArrival
    FROM reservationroomline WITH (nolock) 
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
    WHERE LineNumber IN (
    SELECT reservationroomline.LineNumber
    FROM reservationroomline WITH (nolock) 
    INNER JOIN ReservationRoomLineXTab rrx WITH (NOLOCK) ON rrx.LineNumber = ReservationRoomLine.LineNumber
    INNER JOIN Reservations r WITH (NOLOCK) ON rrx.ReferenceNumber = r.ReferenceNumber 
      AND  rrx.GuestNumber = r.GuestNumber 
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON r.ReferenceNumber = rax.ReferenceNumber
      AND r.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber     
    WHERE
    (
    (
      (  
        (@ARemaining = 1 AND (
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate > @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate > @ARunDate)) -- Year
        ))
        OR 
        (@ARemaining = 0 AND (
          ((@AYear + @AMonth = '') AND (reservationroomline.ArrivalDate = @Arundate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate <= @ARunDate)) -- Year
        ))
      )
      AND (select min(status) 
           from reservations WITH (nolock) 
           inner join reservationroomlinextab WITH (nolock) on reservationroomlinextab.referencenumber = reservations.referencenumber 
             and reservationroomlinextab.guestnumber = reservations.guestnumber 
          where reservationroomlinextab.referencenumber > 0 AND reservationroomlinextab.linenumber = reservationroomline.linenumber) <= 6
      AND ((@AHotelNumberStr = '') OR (reservationroomline.RoomNumber in (Select RoomNumber from Rooms WITH (nolock) where HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr)))))
      AND reservationroomline.Roomnumber is not null
    ) 
    or 
    (
      (  
        (@ARemaining = 1 AND (
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate > @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate > @ARunDate)) -- Year
        ))
        OR 
        (@ARemaining = 0 AND (
          ((@AYear + @AMonth = '') AND (reservationroomline.ArrivalDate = @Arundate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate <= @ARunDate)) -- Year
        ))
      )
      and (select min(status) 
           from reservations WITH (nolock) 
           inner join reservationroomlinextab WITH (nolock) on reservationroomlinextab.referencenumber = reservations.referencenumber 
             and reservationroomlinextab.guestnumber = reservations.guestnumber 
           INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = ReservationRoomLineXtab.ReferenceNumber 
           AND ((@AHotelNumberStr = '') OR (HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
           where reservationroomlinextab.referencenumber > 0 AND reservationroomlinextab.linenumber = reservationroomline.linenumber) <= 6
      and reservationroomline.RoomNumber is null
    )
    )
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((Rooms.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND Rooms.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(reservationroomline.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(reservationroomline.ProductNumber, -666) <> 0)      
    )
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

    SELECT @ATotalNumberOfRoomsRooms = TotalNumberOfRooms 
    FROM #TmpTableArrival
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = TotalNumberOfRooms 
    FROM #TmpTableArrival
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)
  END
  ELSE
  BEGIN
    SELECT sum(numberofrooms) AS cnt, ISNULL(Rooms.RoomTypeNumber, ReservationRoomLine.BookedTypeNumber) AS BookedTypeNumber
    FROM reservationroomline WITH (nolock) 
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = ReservationRoomLine.RoomNumber
    WHERE LineNumber IN (
    SELECT reservationroomline.LineNumber
    FROM reservationroomline WITH (nolock) 
    INNER JOIN ReservationRoomLineXTab rrx WITH (NOLOCK) ON rrx.LineNumber = ReservationRoomLine.LineNumber
    INNER JOIN Reservations r WITH (NOLOCK) ON rrx.ReferenceNumber = r.ReferenceNumber 
      AND  rrx.GuestNumber = r.GuestNumber 
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON r.ReferenceNumber = rax.ReferenceNumber
      AND r.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = ReservationRoomLine.RoomNumber     

    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = ReservationRoomLine.RoomNumber
    WHERE
    (
    (
      (  
        (@ARemaining = 1 AND (
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate > @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate > @ARunDate)) -- Year
        ))
        OR 
        (@ARemaining = 0 AND (
          ((@AYear + @AMonth = '') AND (reservationroomline.ArrivalDate = @Arundate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate <= @ARunDate)) -- Year
        ))
      )
      AND (select min(status) 
           from reservations WITH (nolock) 
           inner join reservationroomlinextab WITH (nolock) on reservationroomlinextab.referencenumber = reservations.referencenumber 
             and reservationroomlinextab.guestnumber = reservations.guestnumber 
          where reservationroomlinextab.referencenumber > 0 AND reservationroomlinextab.linenumber = reservationroomline.linenumber) <= 6
      AND ((@AHotelNumberStr = '') OR (ReservationRoomLine.RoomNumber in (Select RoomNumber from Rooms WITH (nolock) where HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr)))))
      AND ReservationRoomLine.Roomnumber is not null
    ) 
    or 
    (
      (  
        (@ARemaining = 1 AND (
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate > @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate > @ARunDate)) -- Year
        ))
        OR 
        (@ARemaining = 0 AND (
          ((@AYear + @AMonth = '') AND (reservationroomline.ArrivalDate = @Arundate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (reservationroomline.ArrivalDate like @Ayear + @Amonth + '%') AND (reservationroomline.ArrivalDate <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (reservationroomline.ArrivalDate like @Ayear + '%')  AND (reservationroomline.ArrivalDate <= @ARunDate)) -- Year
        ))
      )
      and (select min(status) 
           from reservations WITH (nolock) 
           inner join reservationroomlinextab WITH (nolock) on reservationroomlinextab.referencenumber = reservations.referencenumber 
             and reservationroomlinextab.guestnumber = reservations.guestnumber 
           INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = ReservationRoomLineXtab.ReferenceNumber 
           AND ((@AHotelNumberStr = '') OR (HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
           where reservationroomlinextab.referencenumber > 0 AND reservationroomlinextab.linenumber = reservationroomline.linenumber) <= 6
      and ReservationRoomLine.RoomNumber is null
    )
    )
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((Rooms.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND Rooms.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(reservationroomline.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(reservationroomline.ProductNumber, -666) <> 0)      
    )
    GROUP BY ISNULL(Rooms.RoomTypeNumber, ReservationRoomLine.BookedTypeNumber)
  END
END
ELSE IF @ARunType = 2
BEGIN
  -- Walkin
  IF @ATotal = 1
  BEGIN
    SELECT COUNT(ReservationMain.ReferenceNumber) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableWalkin
    FROM ReservationMain WITH (NOLOCK) 
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationMain.ReferenceNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = rrl.BookedTypeNumber
    WHERE ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ReservationMain.ReferenceNumber IN 
    (
      SELECT (ReferenceNumber)
      FROM Followup WITH (NOLOCK) 
      INNER JOIN ReservationFollowupXTab WITH (NOLOCK) ON ReservationFollowupXTab.FollowupNumber = Followup.FollowupNumber 
        AND GuestNumber = 0 
      WHERE SubType IN (1,4)
        AND 
        (
          ((@AYear + @AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) = @ARunDate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + @AMonth + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) -- Year
        )
      GROUP BY CONVERT(VARCHAR(8), CreatedDate, 112), ReferenceNumber 
      HAVING COUNT(SubType) > 1
    )
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber <> ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

    SELECT @ATotalNumberOfRoomsRooms = NumberOfRooms 
    FROM #TmpTableWalkin
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = NumberOfRooms 
    FROM #TmpTableWalkin
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)

    DROP TABLE #TmpTableWalkin
  END
  ELSE
  BEGIN
    SELECT COUNT(ReservationMain.ReferenceNumber) AS cnt, ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber) AS BookedTypeNumber
    FROM ReservationMain WITH (NOLOCK) 
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationMain.ReferenceNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = rrl.RoomNumber
    WHERE ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ReservationMain.ReferenceNumber IN 
    (
      SELECT (ReferenceNumber)
      FROM Followup WITH (NOLOCK) 
      INNER JOIN ReservationFollowupXTab WITH (NOLOCK) ON ReservationFollowupXTab.FollowupNumber = Followup.FollowupNumber 
        AND GuestNumber = 0 
      WHERE SubType IN (1,4)
        AND 
        (
          ((@AYear + @AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) = @ARunDate)) OR -- Day
          ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + @AMonth + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) OR -- Month
          ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) -- Year
        )
      GROUP BY CONVERT(VARCHAR(8), CreatedDate, 112), ReferenceNumber 
      HAVING COUNT(SubType) > 1
    )
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    GROUP BY ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber)
  END
END
ELSE IF @ARunType = 3
BEGIN
  -- Noshow
  IF @ATotal = 1
  BEGIN
    SELECT count(reservations.referencenumber) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableNoShow
    from followup WITH (nolock)
    inner join reservationfollowupxtab WITH (nolock) on reservationfollowupxtab.followupnumber = followup.followupnumber 
    inner join reservations WITH (nolock) on reservations.referencenumber = reservationfollowupxtab.referencenumber and reservations.guestnumber = reservationfollowupxtab.guestnumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = rrl.BookedTypeNumber
    where Followup.Type IN (5, 12) 
    AND SubType = 3
    and state >= 3 
    and createdby in (
      select useridnumber 
      from personel WITH (nolock) 
      where usertypenumber = 4
    )
    and status = 8
    AND (
      ((@AYear + @AMonth = '') AND (convert(varchar(8), createddate, 112) like @Arundate)) OR -- Day
      ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + @AMonth + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) OR -- Month
      ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) -- Year
    )
    AND ((@AHotelNumberStr = '') OR (Followup.Hotelnumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

    SELECT @ATotalNumberOfRoomsRooms = NumberOfRooms 
    FROM #TmpTableNoShow
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = NumberOfRooms 
    FROM #TmpTableNoShow
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)

    DROP TABLE #TmpTableNoShow
  END
  ELSE
  BEGIN
    select count(reservations.referencenumber) AS cnt, ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber) AS BookedTypeNumber
    from followup WITH (nolock)
    inner join reservationfollowupxtab WITH (nolock) on reservationfollowupxtab.followupnumber = followup.followupnumber 
    inner join reservations WITH (nolock) on reservations.referencenumber = reservationfollowupxtab.referencenumber and reservations.guestnumber = reservationfollowupxtab.guestnumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = rrl.RoomNumber
    where Followup.Type IN (5, 12) 
    AND SubType = 3
    and state >= 3 
    and createdby in (
      select useridnumber 
      from personel WITH (nolock) 
      where usertypenumber = 4
    )
    and status = 8
    AND (
      ((@AYear + @AMonth = '') AND (convert(varchar(8), createddate, 112) like @Arundate)) OR -- Day
      ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + @AMonth + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) OR -- Month
      ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(VARCHAR(8), CreatedDate, 112) LIKE @AYear + '%') AND (CONVERT(VARCHAR(8), CreatedDate, 112) <= @ARunDate)) -- Year
    )
    AND ((@AHotelNumberStr = '') OR (followup.Hotelnumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    GROUP BY ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber)  
  END
END
ELSE IF @ARunType = 4
BEGIN
  -- Dayroom
  DECLARE @DayRoomLaterThanTime VARCHAR(5)
  SELECT @DayRoomLaterThanTime = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'DayRoomsLaterThanTime'
  IF @DayRoomLaterThanTime = ''
    SET @DayRoomLaterThanTime = '13:00'

  IF @ATotal = 1
  BEGIN
    SELECT SUM(SpecNumberOfRooms) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableDayRoom
    FROM TGenerateAvailabilityView WITH (NOLOCK)
    INNER JOIN Reservations r WITH (NOLOCK) ON r.ReferenceNumber = TGenerateAvailabilityView.ReferenceNumber
      AND r.GuestNumber = TGenerateAvailabilityView.GuestNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = r.ReferenceNumber 
      AND rrlx.GuestNumber = r.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON r.ReferenceNumber = rax.ReferenceNumber
      AND r.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = rrl.BookedTypeNumber
    WHERE TGenerateAvailabilityView.Referencenumber > 0
    AND ((@AHotelNumberStr = '') OR (TGenerateAvailabilityView.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ((
      (@ARemaining = 1) AND 
      (
        ((@AYear <> '') AND (@AMonth <> '') AND ((SpecArrivalDate like @Ayear + @Amonth + '%' AND SpecArrivalDate > @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + @Amonth + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))) OR
        ((@AYear <> '') AND (@AMonth = '') AND (SpecArrivalDate like  @Ayear + '%' AND SpecArrivalDate > @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))
      )
    ) OR
      ((@ARemaining = 0) AND 
      (
        ((@AYear + @AMonth = '') AND (SpecArrivalDate = @Arundate AND (SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate = @Arundate AND SpecDepartureTime > @DayRoomLaterThanTime))) OR
        ((@AYear <> '') AND (@AMonth <> '') AND ((SpecDepartureDate like @Ayear + @Amonth + '%' AND SpecDepartureDate <= @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + @Amonth + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))) OR
        ((@AYear <> '') AND (@AMonth = '') AND (SpecDepartureDate like @Ayear + '%') AND SpecDepartureDate <= @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE @Ayear + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))) -- Year
      )
    ))
    AND TGenerateAvailabilityView.Status < 7
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END

    SELECT @ATotalNumberOfRoomsRooms = NumberOfRooms 
    FROM #TmpTableDayRoom
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = NumberOfRooms 
    FROM #TmpTableDayRoom
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)

    DROP TABLE #TmpTableDayRoom
  END
  ELSE
  BEGIN
    SELECT SUM(SpecNumberOfRooms) AS cnt, ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber) AS BookedTypeNumber
    FROM TGenerateAvailabilityView WITH (NOLOCK)
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = TGenerateAvailabilityView.ReferenceNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = rrl.RoomNumber
    WHERE TGenerateAvailabilityView.Referencenumber > 0
    AND ((@AHotelNumberStr = '') OR (TGenerateAvailabilityView.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND ((
      (@ARemaining = 1) AND 
      (
        ((@AYear <> '') AND (@AMonth <> '') AND ((SpecArrivalDate like @Ayear + @Amonth + '%' AND SpecArrivalDate > @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + @Amonth + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))) OR
        ((@AYear <> '') AND (@AMonth = '') AND (SpecArrivalDate like  @Ayear + '%' AND SpecArrivalDate > @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))
      )
    ) OR
      ((@ARemaining = 0) AND 
      (
        ((@AYear + @AMonth = '') AND (SpecArrivalDate = @Arundate AND (SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate = @Arundate AND SpecDepartureTime > @DayRoomLaterThanTime))) OR
        ((@AYear <> '') AND (@AMonth <> '') AND ((SpecArrivalDate like @Ayear + @Amonth + '%' AND SpecArrivalDate <= @Arundate AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE  @Ayear + @Amonth + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))))) OR
        ((@AYear <> '') AND (@AMonth = '') AND (SpecArrivalDate like @Ayear + '%') AND ((SpecArrivalDate = SpecDepartureDate) OR (SpecDepartureDate LIKE @Ayear + '%' AND SpecDepartureTime > @DayRoomLaterThanTime))) -- Year
      )
    ))
    AND TGenerateAvailabilityView.Status < 7
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    GROUP BY ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber)
  END
END
ELSE IF @ARunType = 5
BEGIN
  -- Upgraded
  IF @ATotal = 1
  BEGIN
    SELECT count(*) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableUpg
    FROM RESERVATIONROOMLINE WITH (NOLOCK) 
    INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER 
    INNER JOIN RESERVATIONMAIN WITH (NOLOCK) 
    INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONMAIN.REFERENCENUMBER = RESERVATIONS.ReferenceNumber ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber 
    LEFT OUTER JOIN RESERVATIONADDRESS WITH (NOLOCK) 
    INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER  AND AddressType = 1 ON RESERVATIONS.ReferenceNumber = RESERVATIONADDRESSXTAB.REFERENCENUMBER AND RESERVATIONS.GuestNumber = RESERVATIONADDRESSXTAB.GUESTNUMBER 
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = RESERVATIONROOMLINE.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RESERVATIONROOMLINE.BookedTypeNumber
    WHERE (1=1) 
    AND 
    (
    -- DAY
    ((@AYear + @AMonth = '') AND ((((
    ((RESERVATIONROOMLINE.DepartureDate>=@ARunDate) AND (RESERVATIONROOMLINE.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
    OR  ((RESERVATIONS.DepartureDate>=@ARunDate) AND (RESERVATIONS.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
    AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
    )))
    OR
    -- Month
    ((@AYear <> '') AND (@AMonth <> '') AND (
    (((
    ((convert(varchar(8), RESERVATIONROOMLINE.ArrivalDate, 112) like @Ayear + @Amonth + '%') AND (RESERVATIONROOMLINE.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
    OR  ((convert(varchar(8), RESERVATIONS.ArrivalDate, 112) like @Ayear + @Amonth + '%') AND (RESERVATIONS.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
    AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
    )))
    -- Year
    OR
    ((@AYear <> '') AND (@AMonth = '') AND (
    (((
    ((convert(varchar(8), RESERVATIONROOMLINE.ArrivalDate, 112) like @Ayear + '%') AND (RESERVATIONROOMLINE.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
    OR ((convert(varchar(8), RESERVATIONS.ArrivalDate, 112) like @Ayear + '%') AND (RESERVATIONS.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
    AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
    )))
    )
    AND ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND Reservations.Referencenumber > 0
    AND (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = ReservationRoomLine.BookedTypeNumber) <> (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = (SELECT RoomTypeNumber FROM Rooms WITH (NOLOCK) WHERE Rooms.RoomNumber = ReservationRoomLine.RoomNumber))
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND RESERVATIONADDRESS.LINK IS NOT NULL) AND rOwner.CustomerNumber = RESERVATIONADDRESS.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> 0)      
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END
	
    SELECT @ATotalNumberOfRoomsRooms = NumberOfRooms 
    FROM #TmpTableUpg
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = NumberOfRooms 
    FROM #TmpTableUpg
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)
	
    DROP TABLE #TmpTableUpg      
  END
  ELSE
  BEGIN
    SELECT count(*) AS cnt, ISNULL(Rooms.RoomTypeNumber, ReservationRoomLine.BookedTypeNumber) AS BookedTypeNumber
    FROM RESERVATIONROOMLINE WITH (NOLOCK) 
    INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER 
    INNER JOIN RESERVATIONMAIN WITH (NOLOCK) 
    INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONMAIN.REFERENCENUMBER = RESERVATIONS.ReferenceNumber ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber 
    LEFT OUTER JOIN RESERVATIONADDRESS WITH (NOLOCK) 
    INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER  AND AddressType = 1 ON RESERVATIONS.ReferenceNumber = RESERVATIONADDRESSXTAB.REFERENCENUMBER AND RESERVATIONS.GuestNumber = RESERVATIONADDRESSXTAB.GUESTNUMBER 
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = RESERVATIONROOMLINE.RoomNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = ReservationRoomLine.RoomNumber
    WHERE (1=1) 
    AND 
    (
    -- DAY
    ((@AYear + @AMonth = '') AND ((((
    ((RESERVATIONROOMLINE.DepartureDate>=@ARunDate) AND (RESERVATIONROOMLINE.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
    OR  ((RESERVATIONS.DepartureDate>=@ARunDate) AND (RESERVATIONS.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
    AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
    )))
    OR
    -- Month
    ((@AYear <> '') AND (@AMonth <> '') AND (
    (((
    ((convert(varchar(8), RESERVATIONROOMLINE.ArrivalDate, 112) like @Ayear + @Amonth + '%') AND (RESERVATIONROOMLINE.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
    OR  ((convert(varchar(8), RESERVATIONS.ArrivalDate, 112) like @Ayear + @Amonth + '%') AND (RESERVATIONS.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
    AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
    )))
    -- Year
    OR
    ((@AYear <> '') AND (@AMonth = '') AND (
    (((
    ((convert(varchar(8), RESERVATIONROOMLINE.ArrivalDate, 112) like @Ayear + '%') AND (RESERVATIONROOMLINE.ArrivalDate <= @ARunDate) AND (Reservations.GuestNumber = 0)) 
    OR ((convert(varchar(8), RESERVATIONS.ArrivalDate, 112) like @Ayear + '%') AND (RESERVATIONS.ArrivalDate <= @ARunDate)) AND (Reservations.GuestNumber > 0))) 
    AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
    )))
    )
    AND ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    AND Reservations.Referencenumber > 0
    AND (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = ReservationRoomLine.BookedTypeNumber) <> (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = (SELECT RoomTypeNumber FROM Rooms WITH (NOLOCK) WHERE Rooms.RoomNumber = ReservationRoomLine.RoomNumber))
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND RESERVATIONADDRESS.LINK IS NOT NULL) AND rOwner.CustomerNumber = RESERVATIONADDRESS.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(ReservationRoomLine.ProductNumber, -666) <> 0)      
    GROUP BY ISNULL(Rooms.RoomTypeNumber, ReservationRoomLine.BookedTypeNumber)
  END
END
ELSE IF @ARunType = 6
BEGIN
  -- Early departure
  IF @ATotal = 1
  BEGIN
    SELECT COUNT(StatisticLeads.NumberOfRooms) AS NumberOfRooms, CAST(CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END AS BIT) AS RoomBedType
    INTO #TmpTableEarly
    FROM StatisticLeads WITH (NOLOCK)
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = StatisticLeads.ReferenceNumber
      AND ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    LEFT OUTER JOIN Reservations WITH (NOLOCK) ON StatisticLeads.ReferenceNumber = Reservations.ReferenceNumber 
      AND StatisticLeads.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = rrl.BookedTypeNumber
    WHERE 1=1 AND
    (
      ((@AYear + @AMonth = '') AND (CONVERT(CHAR(8), SystemDateTime, 112) = @ARunDate)) OR
      ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(CHAR(8), SystemDateTime, 112) LIKE @AYear + @AMonth + '%')) OR
      ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(CHAR(8), SystemDateTime, 112) LIKE @AYear + '%'))
    )
    AND Reservations.Status > 5 
    AND StatisticLeads.Type = 1
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)
    GROUP BY CASE WHEN RoomTypes.ParentRoomTypeNumber IS NULL THEN 0 ELSE 1 END
	
    SELECT @ATotalNumberOfRoomsRooms = NumberOfRooms 
    FROM #TmpTableEarly
    WHERE RoomBedType = 0

    SELECT @ATotalNumberOfRoomsBeds = NumberOfRooms 
    FROM #TmpTableEarly
    WHERE RoomBedType = 1

    SET @ATotalNumberOfRooms = ISNULL(@ATotalNumberOfRoomsRooms, 0) + ISNULL(@ATotalNumberOfRoomsBeds, 0)
	
    DROP TABLE #TmpTableEarly      
  END
  ELSE
  BEGIN
    SELECT COUNT(StatisticLeads.NumberOfRooms) AS cnt, ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber) AS BookedTypeNumber
    FROM StatisticLeads WITH (NOLOCK)
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = StatisticLeads.ReferenceNumber
      AND ((@AHotelNumberStr = '') OR (ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberStr))))
    LEFT OUTER JOIN Reservations WITH (NOLOCK) ON StatisticLeads.ReferenceNumber = Reservations.ReferenceNumber 
      AND StatisticLeads.GuestNumber = Reservations.GuestNumber
    INNER JOIN ReservationRoomLineXTab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = Reservations.ReferenceNumber 
      AND rrlx.GuestNumber = Reservations.GuestNumber 
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
    LEFT OUTER JOIN ReservationAddressXtab rax WITH (nolock) ON Reservations.ReferenceNumber = rax.ReferenceNumber
      AND Reservations.GuestNumber = rax.GuestNumber 
    LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber
    LEFT OUTER JOIN Rooms rOwner WITH (NOLOCK) ON rOwner.RoomNumber = rrl.RoomNumber
    LEFT OUTER JOIN Rooms WITH (NOLOCK) on Rooms.RoomNumber = rrl.RoomNumber
    WHERE 1=1 AND
    (
      ((@AYear + @AMonth = '') AND (CONVERT(CHAR(8), SystemDateTime, 112) = @ARunDate)) OR
      ((@AYear <> '') AND (@AMonth <> '') AND (CONVERT(CHAR(8), SystemDateTime, 112) LIKE @AYear + @AMonth + '%')) OR
      ((@AYear <> '') AND (@AMonth = '') AND (CONVERT(CHAR(8), SystemDateTime, 112) LIKE @AYear + '%'))
    )
    AND Reservations.Status > 5 
    AND StatisticLeads.Type = 1
    AND ra.AddressType = 1
    AND (@AReportOwnerReservation = 2 OR 
      CASE WHEN ((rOwner.CustomerNumber IS NOT NULL AND ra.LINK IS NOT NULL) AND rOwner.CustomerNumber = ra.LINK) THEN 1 ELSE 0 END = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(rrl.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(rrl.ProductNumber, -666) <> 0)      
    GROUP BY ISNULL(Rooms.RoomTypeNumber, rrl.BookedTypeNumber)
  END
END