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), @AWeekDayCSV VARCHAR(25), @ACompareToCreate BIT 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:
    KJ 20250319: Bug: Fix for "Month not sorted correct"
    KJ 20240606: Bug: Fix for "Wrong lead for name list reservation. Always use main reservation"
    20221229: Ext: Only include selected weekdays. Req #4142
    20211119: Added group by Country. Req #3998
*/

CREATE TABLE #SelectWeekDays 
(
  WeekDayNumber INT
)
IF @AWeekDayCSV <> ''
  INSERT INTO #SelectWeekDays SELECT Number + 1 FROM dbo.iter_intlist_to_table_No_Dummy(@AWeekDayCSV)
SET DATEFIRST 1 -- Monday

SELECT 
Reservations.ReferenceNumber, Reservations.GuestNumber, 
SUM(  
  DATEDIFF(
    DAY,
    CASE WHEN @ACompareToCreate = 1 THEN resMain.ReservationCreateDate ELSE ISNULL(ReservationsExt.ConfirmDateStamp, resMain.ReservationCreateDate) END,
    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
  CASE WHEN @ACompareToCreate = 1 THEN CONVERT(VARCHAR, DATEPART(YEAR, resMain.ReservationCreateDate)) ELSE CONVERT(VARCHAR, DATEPART(YEAR, ISNULL(ReservationsExt.ConfirmDateStamp, resMain.ReservationCreateDate))) END
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
      CASE WHEN @ACompareToCreate = 1 THEN CONVERT(VARCHAR, DATEPART(MONTH, resMain.ReservationCreateDate)) ELSE CONVERT(VARCHAR, DATEPART(MONTH, ISNULL(ReservationsExt.ConfirmDateStamp, resMain.ReservationCreateDate))) END
    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
      CASE WHEN @ACompareToCreate = 1 THEN RIGHT('00' + CONVERT(VARCHAR, DATEPART(WEEK, resMain.ReservationCreateDate)), 2) ELSE RIGHT('00' + CONVERT(VARCHAR, DATEPART(WEEK, ISNULL(ReservationsExt.ConfirmDateStamp, resMain.ReservationCreateDate))), 2) END
    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
      CASE WHEN @ACompareToCreate = 1 THEN CONVERT(CHAR(8), resMain.ReservationCreateDate, 112) ELSE CONVERT(CHAR(8), ISNULL(ReservationsExt.ConfirmDateStamp, resMain.ReservationCreateDate), 112) END
    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
INNER JOIN Reservations resMain WITH (NOLOCK) ON resMain.ReferenceNumber = Reservations.ReferenceNumber
  AND resMain.GuestNumber = 0
LEFT OUTER JOIN ReservationsExt WITH (NOLOCK) ON ReservationsExt.FK_ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationsExt.FK_GuestNumber = 0
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), CASE WHEN @ACompareToCreate = 1 THEN resMain.ReservationCreateDate ELSE ISNULL(ReservationsExt.ConfirmDateStamp, resMain.ReservationCreateDate) END, 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
AND (@AWeekDayCSV = '' OR DATEPART(WEEKDAY, Reservations.ArrivalDate) NOT IN (SELECT WeekDayNumber FROM #SelectWeekDays))
GROUP BY Reservations.ReferenceNumber, Reservations.GuestNumber, ReservationRoomLine.ArrivalDate, Reservations.ArrivalDate, Reservations.ReservationCreateDate, ReservationsExt.ConfirmDateStamp,
  Segments.Text, PurposeCodes.Text, PointOfSales.Text, GuestTypes.Text, Countries.CountryName, resMain.ReservationCreateDate

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

DROP TABLE #tmp