CREATE PROCEDURE [dbo].[WareStatReport] @AHotelNumberCSV VARCHAR(500), @AFromDate DATETIME, @AToDate DATETIME AS
SET NOCOUNT ON
/*
  Created by Kent Johansen, March 2020
  Get number of booked/sold wares

  Changes:

*/
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF(
  (SELECT ',' + QUOTENAME(WareName + '_' + CAST(WareNumber AS VARCHAR)) FROM Ware WITH (NOLOCK) WHERE UseInWareReportExport = 1 GROUP BY SortNumber, WareNumber, WareName ORDER BY SortNumber, WareNumber, WareName FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 
  1, 
  1, 
  ''
);

SELECT DISTINCT ArrangementLines.ArrangementLineNumber, ArrangementLines.Date, Ware.WareName + '_' + CAST(Ware.WareNumber AS VARCHAR) AS WName, 
ArrangementLines.Pieces
INTO #tmp
FROM ArrangementReservationsXtab  WITH (NOLOCK)
INNER JOIN Reservations  WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber
  AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
  AND Reservations.Status <= 6 -- No cancelled or noshow
INNER JOIN ReservationMain  WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationMain.ReferenceNumber
  AND (@AHotelNumberCSV = '' OR ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AHotelNumberCSV)))
INNER JOIN ArrangementLines  WITH (NOLOCK) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
  AND ArrangementLines.WareNumber > 0
  AND ArrangementLines.[Date] >= @AFromDate
  AND ArrangementLines.[Date] <= @AToDate
INNER JOIN Ware  WITH (NOLOCK) ON ArrangementLines.WareNumber = Ware.WareNumber
  AND Ware.Type in (0)
  AND Ware.UseInWareReportExport = 1
WHERE DBO.ArrangementLineWithinPeriod(ArrangementReservationsXtab.ReferenceNumber,ArrangementLines.Date) = 1

SET @query = N'

WITH T AS
(
  SELECT Date, WName, SUM(Pieces) AS Pieces FROM #tmp GROUP BY Date, WName
)

SELECT * 
FROM T 
PIVOT 
(
  SUM(Pieces) FOR WName IN (' + @cols + ')
) AS pvt
ORDER BY Date'

exec sp_executesql @query;

DROP TABLE #tmp