CREATE PROCEDURE dbo.MISBookedPerDay @AFromDate DATETIME, @AToDate DATETIME, @AHotelCustomerNumberCSV VARCHAR(100) AS
SET NOCOUNT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

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

SELECT @cols = STUFF(
  (SELECT ',' + QUOTENAME('Rev_AccountGrp' + Name) FROM TurnOverGroups WITH (NOLOCK) GROUP BY Name, Number ORDER BY Number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 
  1, 
  1, 
  ''
);

SET @query = N'

DECLARE @LInclProvision BIT

SELECT @LInclProvision = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = ''IncludeProvisionInMIS''
SET @LInclProvision = ISNULL(@LInclProvision, 0);

WITH T AS
(
  SELECT MISTableView.DateStamp, ''Rev_AccountGrp'' + CAST(TurnOverGroups.Name AS VARCHAR(100)) AS tgName, 
  SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat) AS BookedPrice
  FROM MISTableView WITH (NOLOCK)
  INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = MISTableView.AccountNumber
  INNER JOIN TurnOverGroups WITH (NOLOCK) ON TurnOverGroups.Number = Accounts.TurnOverGroupNumber
  WHERE (@LInclProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND DateStamp >= ''' + CONVERT(CHAR(8), @AFromDate, 112) + '''
  AND DateStamp <= ''' + CONVERT(CHAR(8), @AToDate, 112) + ''''
  IF @AHotelCustomerNumberCSV <> ''
    SET @query = @query + ' AND MISTableView.HotelCustomerNumber IN (' + @AHotelCustomerNumberCSV + ')'
  SET @query = @query + ' GROUP BY MISTableView.DateStamp, TurnOverGroups.Name
)

SELECT * 
FROM T 
PIVOT 
(
  SUM(BookedPrice) FOR tgName IN (' + @cols + ')
) AS pvt
ORDER BY DateStamp'

exec sp_executesql @query;