CREATE PROCEDURE dbo.LeadTimeReport @AFromDate CHAR(8), @AToDate CHAR(8), @AHotelNumberCSV VARCHAR(500), @AReservationTypeCSV VARCHAR(25), @AReservationStatusCSV VARCHAR(25),
  @ASegmentNumberCSV VARCHAR(1000), @APurposeNumberCSV VARCHAR(500), @APOSCSV VARCHAR(500), @AGuestTypeCSV VARCHAR(500), @APerArrivalDate BIT, @ATotalPerType TINYINT, @AGroupByType TINYINT, @ACountryCSV VARCHAR(1000) AS
/*
  Created by Kent Johansen, July 2020
  @APerArrivalDate: 0=Per create date, 1=Per arrival date
  @ATotalPerType: 0=Total per month, 1=Total per week, 2=Total per day
  @AGroupByType: 0=Segment, 1=Purpose, 2=POS, 3=Guest type, 4=Country

  Changes:
    20211119: Added group by Country. Req #3998
*/

SELECT 
Reservations.ReferenceNumber, Reservations.GuestNumber, 
SUM(  
  DATEDIFF(
    DAY,
    Reservations.ReservationCreateDate,
    CASE WHEN Reservations.GuestNumber = 0 THEN 
      CONVERT(DATETIME, ReservationRoomLine.ArrivalDate, 112) 
    ELSE 
      CONVERT(DATETIME, Reservations.ArrivalDate, 112) 
    END
  ) * 
  (CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.NumberOfRooms ELSE 1 END)
) AS LeadDays,

CAST(SUM((CASE WHEN Reservations.GuestNumber = 0 THEN ReservationRoomLine.NumberOfRooms ELSE 1 END)) AS DECIMAL) AS NumberOfRooms,  

CASE WHEN @APerArrivalDate = 1 THEN 
  CASE WHEN Reservations.GuestNumber = 0 THEN 
    CONVERT(VARCHAR, DATEPART(YEAR, ReservationRoomLine.ArrivalDate))
  ELSE 
    CONVERT(VARCHAR, DATEPART(YEAR, Reservations.ArrivalDate))
  END
ELSE
  CONVERT(VARCHAR, DATEPART(YEAR, Reservations.ReservationCreateDate))
END AS ShowYear,
CASE 
  WHEN @ATotalPerType = 0 THEN
    CASE WHEN @APerArrivalDate = 1 THEN 
      CASE WHEN Reservations.GuestNumber = 0 THEN 
        CONVERT(VARCHAR, DATEPART(MONTH, ReservationRoomLine.ArrivalDate))
      ELSE 
        CONVERT(VARCHAR, DATEPART(MONTH, Reservations.ArrivalDate))
      END
    ELSE
      CONVERT(VARCHAR, DATEPART(MONTH, Reservations.ReservationCreateDate))
    END
  WHEN @ATotalPerType = 1 THEN
    CASE WHEN @APerArrivalDate = 1 THEN 
      CASE WHEN Reservations.GuestNumber = 0 THEN 
        RIGHT('00' + CONVERT(VARCHAR, DATEPART(WEEK, ReservationRoomLine.ArrivalDate)), 2)
      ELSE 
        RIGHT('00' + CONVERT(VARCHAR, DATEPART(WEEK, Reservations.ArrivalDate)), 2)        
      END
    ELSE
      RIGHT('00' + CONVERT(VARCHAR, DATEPART(WEEK, Reservations.ReservationCreateDate)), 2)        
      
    END
  WHEN @ATotalPerType = 2 THEN
    CASE WHEN @APerArrivalDate = 1 THEN 
      CASE WHEN Reservations.GuestNumber = 0 THEN 
        CONVERT(CHAR(8), ReservationRoomLine.ArrivalDate, 112) 
      ELSE 
        CONVERT(CHAR(8), Reservations.ArrivalDate, 112) 
      END
    ELSE
      CONVERT(CHAR(8), Reservations.ReservationCreateDate, 112)
    END
END AS ShowPeriod,
CASE 
  WHEN @AGroupByType = 0 THEN Segments.Text
  WHEN @AGroupByType = 1 THEN PurposeCodes.Text
  WHEN @AGroupByType = 2 THEN PointOfSales.Text
  WHEN @AGroupByType = 3 THEN GuestTypes.Text
  WHEN @AGroupByType = 4 THEN Countries.CountryName
END AS GroupBy
INTO #tmp
FROM Reservations WITH (NOLOCK)
INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber
INNER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  AND (@AHotelNumberCSV = '' OR ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberCSV)))
  AND ReservationMain.ReservationType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationTypeCSV))
LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = Reservations.MarketSegment
LEFT OUTER JOIN PurposeCodes WITH (NOLOCK) ON PurposeCodes.PurposeCodeNumber = Reservations.PurposeCode
LEFT OUTER JOIN PointOfSales WITH (NOLOCK) ON PointOfSales.Number = Reservations.PointOfSale
LEFT OUTER JOIN GuestTypes WITH (NOLOCK) ON GuestTypes.TypeNumber = Reservations.GuestType
LEFT OUTER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
LEFT OUTER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
LEFT OUTER JOIN Countries WITH (NOLOCK) ON Countries.CountryNumber = ReservationAddress.CountryNumber
WHERE 1 = 1
AND (
  (@APerArrivalDate = 1 AND
    (
      (
        Reservations.GuestNumber = 0 AND ReservationRoomLine.ArrivalDate BETWEEN @AFromDate AND @AToDate
      ) 
      OR
      (
        Reservations.GuestNumber > 0 AND Reservations.ArrivalDate BETWEEN @AFromDate AND @AToDate
      ) 
    )
  )
  OR (@APerArrivalDate = 0 AND CONVERT(CHAR(8), Reservations.ReservationCreateDate, 112) BETWEEN @AFromDate AND @AToDate)
)
AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AReservationStatusCSV))
AND Reservations.Referencenumber > 0
AND (@ASegmentNumberCSV = '' OR Reservations.MarketSegment IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
AND (@APurposeNumberCSV = '' OR Reservations.PurposeCode IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@APurposeNumberCSV)))
AND (@APOSCSV = '' OR Reservations.PointOfSale IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@APOSCSV)))
AND (@AGuestTypeCSV = '' OR Reservations.GuestType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AGuestTypeCSV)))
AND (@ACountryCSV = '' OR ReservationAddress.CountryNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@ACountryCSV)))
AND ReservationAddressXTab.AddressTypeNumber = 1
GROUP BY Reservations.ReferenceNumber, Reservations.GuestNumber, ReservationRoomLine.ArrivalDate, Reservations.ArrivalDate, Reservations.ReservationCreateDate,
  Segments.Text, PurposeCodes.Text, PointOfSales.Text, GuestTypes.Text, Countries.CountryName

SELECT SUM(LeadDays) / SUM(NumberOfRooms) AS LeadDays, ShowYear + ShowPeriod AS Period, GroupBy, ShowYear, ShowPeriod
FROM #tmp
GROUP BY ShowYear, ShowPeriod, GroupBy
ORDER BY ShowYear + ShowPeriod, GroupBy

DROP TABLE #tmp