CREATE PROCEDURE dbo.TicketStatisticsReport @AFromDate DATETIME, @AToDate DATETIME, @AHotelCustomerNumberCSV VARCHAR(500), @AStatusCSV VARCHAR(50), @AWareGroupNumberCSV VARCHAR(5000), @AWareNumberCSV VARCHAR(MAX), @AGroupType SMALLINT, @AIntervalMinute INT AS
/*
  Created by: Kent Johansen, December 2023.
  Remarks:
  @AGroupType: 0=Per minutes, 1=Per day, 2=Per week
  Changes:
*/
SET NOCOUNT ON

SELECT CAST(TicketStatistics.DateStamp AS DATE) AS Date1, ArrangementLines.WareNumber,
DATEPART(WEEK, TicketStatistics.DateStamp) AS WeekNo,
CASE WHEN DATEPART(MINUTE, TicketStatistics.DateStamp) < @AIntervalMinute THEN
  DATEADD(HOUR, DATEDIFF(HOUR, 0, TicketStatistics.DateStamp) + 0, 0)
ELSE
  DATEADD(MINUTE, @AIntervalMinute, DATEADD(HOUR, DATEDIFF(HOUR, 0, TicketStatistics.DateStamp) + 0, 0))
END AS DateStamp,
SUM(TicketStatistics.NumberOfAdult) AS NumberOfAdult, SUM(TicketStatistics.NumberOfChildren1) AS NumberOfChildren1, SUM(TicketStatistics.NumberOfChildren2) AS NumberOfChildren2,
SUM(TicketStatistics.NumberOfAdult) + SUM(TicketStatistics.NumberOfChildren1) + SUM(TicketStatistics.NumberOfChildren2) AS Pax
INTO #tmp
FROM TicketStatistics WITH (NOLOCK)
INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementLines.ArrangementLineUniqueID = TicketStatistics.FK_ArrangementUniqueId
  AND ArrangementLines.WareNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareNumberCSV))
INNER JOIN ArrangementReservationsXTab WITH (NOLOCK) ON ArrangementReservationsXTab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ArrangementReservationsXtab.ReferenceNumber
  AND Reservations.GuestNumber = ArrangementReservationsXtab.GuestNumber
  AND Reservations.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AStatusCSV))
  AND Reservations.ReferenceNumber > 0
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationMain.FkCustomerHotel IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
WHERE TicketStatistics.DateStamp BETWEEN @AFromDate AND @AToDate
GROUP BY TicketStatistics.DateStamp, ArrangementLines.WareNumber

IF @AGroupType = 0 -- Per minute
BEGIN
  SELECT TimeSlot.StartDate AS DateStamp, 
  (
    SELECT SUM(NumberOfAdult)
    FROM #tmp
    WHERE #tmp.DateStamp >= TimeSlot.StartDate 
    AND #tmp.DateStamp < TimeSlot.EndDate
    AND #tmp.WareNumber = TimeSlot.WareNumber
  ) AS NumberOfAdult,
  (
    SELECT SUM(NumberOfChildren1)
    FROM #tmp
    WHERE #tmp.DateStamp >= TimeSlot.StartDate 
    AND #tmp.DateStamp < TimeSlot.EndDate
    AND #tmp.WareNumber = TimeSlot.WareNumber
  ) AS NumberOfChildren1,
  (
    SELECT SUM(NumberOfChildren2)
    FROM #tmp
    WHERE #tmp.DateStamp >= TimeSlot.StartDate 
    AND #tmp.DateStamp < TimeSlot.EndDate
    AND #tmp.WareNumber = TimeSlot.WareNumber
  ) AS NumberOfChildren2,
  (
    SELECT SUM(Pax)
    FROM #tmp
    WHERE #tmp.DateStamp >= TimeSlot.StartDate 
    AND #tmp.DateStamp < TimeSlot.EndDate
    AND #tmp.WareNumber = TimeSlot.WareNumber
  ) AS Pax,
  WareGroups.WareGroupNumber, WareGroups.SortNumber AS WareGroupSortNumber, #tmp.WareNumber, Ware.SortNumber AS WareSortNumber, Ware.WareName, (SELECT COUNT(DISTINCT WareNumber) FROM #tmp) AS NumberOfWares, 0 AS cntType
  FROM #tmp
  CROSS APPLY dbo.TimeSlot(@AFromDate, @AToDate, (@AIntervalMinute*60), #tmp.WareNumber) TimeSlot
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = #tmp.WareNumber
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
    AND WareGroups.WareGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareGroupNumberCSV))
  GROUP BY TimeSlot.StartDate, TimeSlot.EndDate, TimeSlot.WareNumber, WareGroups.WareGroupNumber, WareGroups.SortNumber, #tmp.WareNumber, Ware.SortNumber, Ware.WareName

  UNION

  SELECT TimeSlot.StartDate AS DateStamp, 
  (
    SELECT SUM(NumberOfAdult)
    FROM #tmp
    WHERE #tmp.DateStamp >= TimeSlot.StartDate 
    AND #tmp.DateStamp < TimeSlot.EndDate
  ) AS NumberOfAdult,
  (
    SELECT SUM(NumberOfChildren1)
    FROM #tmp
    WHERE #tmp.DateStamp >= TimeSlot.StartDate 
    AND #tmp.DateStamp < TimeSlot.EndDate
  ) AS NumberOfChildren1,
  (
    SELECT SUM(NumberOfChildren2)
    FROM #tmp
    WHERE #tmp.DateStamp >= TimeSlot.StartDate 
    AND #tmp.DateStamp < TimeSlot.EndDate
  ) AS NumberOfChildren2,
  (
    SELECT SUM(Pax)
    FROM #tmp
    WHERE #tmp.DateStamp >= TimeSlot.StartDate 
    AND #tmp.DateStamp < TimeSlot.EndDate
  ) AS Pax,
  -1 AS WareGroupNumber, -1 AS WareGroupSortNumber, -1 AS WareNumber, -1 AS WareSortNumber, '' AS WareName, -1 AS NumberOfWares, 1 AS cntType
  FROM #tmp
  CROSS APPLY dbo.TimeSlot(@AFromDate, @AToDate, (@AIntervalMinute*60), -1) TimeSlot
  GROUP BY TimeSlot.StartDate, TimeSlot.EndDate

  UNION

  SELECT @AFromDate AS DateStamp,
  SUM(#tmp.NumberOfAdult) AS NumberOfAdult, SUM(NumberOfChildren1) AS NumberOfChildren1, SUM(NumberOfChildren2) AS NumberOfChildren2, SUM(Pax) AS Pax,
  WareGroups.WareGroupNumber, WareGroups.SortNumber AS WareGroupSortNumber, #tmp.WareNumber, Ware.SortNumber AS WareSortNumber, Ware.WareName, (SELECT COUNT(DISTINCT WareNumber) FROM #tmp) AS NumberOfWares, 2 AS cntType
  FROM #tmp
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = #tmp.WareNumber
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
    AND WareGroups.WareGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareGroupNumberCSV))
  GROUP BY WareGroups.WareGroupNumber, WareGroups.SortNumber, #tmp.WareNumber, Ware.SortNumber, Ware.WareName
  ORDER BY cntType, WareGroupSortNumber, WareSortNumber
END
ELSE IF @AGroupType = 1 -- Per day
BEGIN
  SELECT dtRange.IndividualDate AS DateStamp, 
  (
    SELECT SUM(NumberOfAdult)
    FROM #tmp
    WHERE #tmp.Date1 = dtRange.IndividualDate 
    AND #tmp.WareNumber = dtRange.PacketNumber
  ) AS NumberOfAdult,
  (
    SELECT SUM(NumberOfChildren1)
    FROM #tmp
    WHERE #tmp.Date1 = dtRange.IndividualDate
    AND #tmp.WareNumber = dtRange.PacketNumber
  ) AS NumberOfChildren1,
  (
    SELECT SUM(NumberOfChildren2)
    FROM #tmp
    WHERE #tmp.Date1 = dtRange.IndividualDate
    AND #tmp.WareNumber = dtRange.PacketNumber
  ) AS NumberOfChildren2,
  (
    SELECT SUM(Pax)
    FROM #tmp
    WHERE #tmp.Date1 = dtRange.IndividualDate
    AND #tmp.WareNumber = dtRange.PacketNumber
  ) AS Pax,
  WareGroups.WareGroupNumber, WareGroups.SortNumber AS WareGroupSortNumber, #tmp.WareNumber, Ware.SortNumber AS WareSortNumber, Ware.WareName, (SELECT COUNT(DISTINCT WareNumber) FROM #tmp) AS NumberOfWares, 0 AS cntType
  FROM #tmp
  CROSS APPLY dbo.DateRange(@AFromDate, @AToDate, #tmp.WareNumber) dtRange
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = #tmp.WareNumber
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
    AND WareGroups.WareGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareGroupNumberCSV))
  GROUP BY dtRange.IndividualDate, dtRange.PacketNumber, WareGroups.WareGroupNumber, WareGroups.SortNumber, #tmp.WareNumber, Ware.SortNumber, Ware.WareName

  UNION

  SELECT dtRange.IndividualDate AS DateStamp,
  (
    SELECT SUM(NumberOfAdult)
    FROM #tmp
    WHERE #tmp.Date1 = dtRange.IndividualDate 
  ) AS NumberOfAdult,
  (
    SELECT SUM(NumberOfChildren1)
    FROM #tmp
    WHERE #tmp.Date1 = dtRange.IndividualDate
  ) AS NumberOfChildren1,
  (
    SELECT SUM(NumberOfChildren2)
    FROM #tmp
    WHERE #tmp.Date1 = dtRange.IndividualDate
  ) AS NumberOfChildren2,
  (
    SELECT SUM(Pax)
    FROM #tmp
    WHERE #tmp.Date1 = dtRange.IndividualDate
  ) AS Pax,
  -1 AS WareGroupNumber, -1 AS WareGroupSortNumber, -1 AS WareNumber, -1 AS WareSortNumber, '' AS WareName, -1 AS NumberOfWares, 1 AS cntType
  FROM #tmp
  CROSS APPLY dbo.DateRange(@AFromDate, @AToDate, #tmp.WareNumber) dtRange
  GROUP BY dtRange.IndividualDate

  UNION

  SELECT #tmp.Date1,
  SUM(#tmp.NumberOfAdult) AS NumberOfAdult, SUM(NumberOfChildren1) AS NumberOfChildren1, SUM(NumberOfChildren2) AS NumberOfChildren2, SUM(Pax) AS Pax,
  WareGroups.WareGroupNumber, WareGroups.SortNumber AS WareGroupSortNumber, #tmp.WareNumber, Ware.SortNumber AS WareSortNumber, Ware.WareName, (SELECT COUNT(DISTINCT WareNumber) FROM #tmp) AS NumberOfWares, 2 AS cntType
  FROM #tmp
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = #tmp.WareNumber
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
    AND WareGroups.WareGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareGroupNumberCSV))
  GROUP BY #tmp.Date1, WareGroups.WareGroupNumber, WareGroups.SortNumber, #tmp.WareNumber, Ware.SortNumber, Ware.WareName
  ORDER BY cntType, DateStamp, WareGroupSortNumber, WareSortNumber
END
ELSE IF @AGroupType = 2 -- Per week
BEGIN
  SELECT WeekNo,
  (
    SELECT SUM(NumberOfAdult)
    FROM #tmp
    WHERE DATEPART(WEEK, #tmp.Date1) = WeekNo 
    AND #tmp.WareNumber = dtRange.PacketNumber
  ) AS NumberOfAdult,
  (
    SELECT SUM(NumberOfChildren1)
    FROM #tmp
    WHERE DATEPART(WEEK, #tmp.Date1) = WeekNo 
    AND #tmp.WareNumber = dtRange.PacketNumber
  ) AS NumberOfChildren1,
  (
    SELECT SUM(NumberOfChildren2)
    FROM #tmp
    WHERE DATEPART(WEEK, #tmp.Date1) = WeekNo 
    AND #tmp.WareNumber = dtRange.PacketNumber
  ) AS NumberOfChildren2,
  (
    SELECT SUM(Pax)
    FROM #tmp
    WHERE DATEPART(WEEK, #tmp.Date1) = WeekNo 
    AND #tmp.WareNumber = dtRange.PacketNumber
  ) AS Pax,
  WareGroups.WareGroupNumber, WareGroups.SortNumber AS WareGroupSortNumber, #tmp.WareNumber, Ware.SortNumber AS WareSortNumber, Ware.WareName, (SELECT COUNT(DISTINCT WareNumber) FROM #tmp) AS NumberOfWares, 0 AS cntType
  FROM #tmp
  CROSS APPLY dbo.DateRange(@AFromDate, @AToDate, #tmp.WareNumber) dtRange
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = #tmp.WareNumber
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
    AND WareGroups.WareGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareGroupNumberCSV))
  GROUP BY WeekNo, dtRange.PacketNumber, WareGroups.WareGroupNumber, WareGroups.SortNumber, #tmp.WareNumber, Ware.SortNumber, Ware.WareName

  UNION

  SELECT WeekNo,
  (
    SELECT SUM(NumberOfAdult)
    FROM #tmp
    WHERE DATEPART(WEEK, #tmp.Date1) = WeekNo 
  ) AS NumberOfAdult,
  (
    SELECT SUM(NumberOfChildren1)
    FROM #tmp
    WHERE DATEPART(WEEK, #tmp.Date1) = WeekNo 
  ) AS NumberOfChildren1,
  (
    SELECT SUM(NumberOfChildren2)
    FROM #tmp
    WHERE DATEPART(WEEK, #tmp.Date1) = WeekNo 
  ) AS NumberOfChildren2,
  (
    SELECT SUM(Pax)
    FROM #tmp
    WHERE DATEPART(WEEK, #tmp.Date1) = WeekNo 
  ) AS Pax,
  -1 AS WareGroupNumber, -1 AS WareGroupSortNumber, -1 AS WareNumber, -1 AS WareSortNumber, '' AS WareName, -1 AS NumberOfWares, 1 AS cntType
  FROM #tmp
  CROSS APPLY dbo.DateRange(@AFromDate, @AToDate, #tmp.WareNumber) dtRange
  GROUP BY WeekNo

  UNION

  SELECT #tmp.WeekNo,
  SUM(#tmp.NumberOfAdult) AS NumberOfAdult, SUM(NumberOfChildren1) AS NumberOfChildren1, SUM(NumberOfChildren2) AS NumberOfChildren2, SUM(Pax) AS Pax,
  WareGroups.WareGroupNumber, WareGroups.SortNumber AS WareGroupSortNumber, #tmp.WareNumber, Ware.SortNumber AS WareSortNumber, Ware.WareName, (SELECT COUNT(DISTINCT WareNumber) FROM #tmp) AS NumberOfWares, 2 AS cntType
  FROM #tmp
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = #tmp.WareNumber
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
    AND WareGroups.WareGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AWareGroupNumberCSV))
  GROUP BY #tmp.WeekNo, WareGroups.WareGroupNumber, WareGroups.SortNumber, #tmp.WareNumber, Ware.SortNumber, Ware.WareName
  ORDER BY cntType, WeekNo, WareGroupSortNumber, WareSortNumber
END

DROP TABLE #tmp