Good Morning,
I've put together a Query together to gather some Data that I can run on Microsoft Excel and use the data in a pivot table. What I am finding though is that when I reference the pivot table for the "Date" Column on a separate work sheet (Date format is DD/MM/YYYY on the sheet, but displays as YYYY-MM-DD in the pivot Table) I constantly get a REF error. Would It be best to place a Format command in the Query or should I be looking somewhere else to fix this error? I've attached the code below and any help would be appreciated!
SET NOCOUNT ON
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @DATES datetime
DECLARE @DATEE datetime
SET @DATES = CONVERT(DATE,GETDATE()-40)
SET @DATEE = CONVERT(DATE,GETDATE()-1)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE [MSADailyStats](
[Date] [date],
[Rooms] [integer],
[Revenue] [money],
[OOO] [integer])
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO [MSADailyStats]
([Date],[Rooms])
SELECT [stats_Date],SUM([Nights]) AS [Nights]
FROM [Daily_Statistics]
WHERE [stats_Date] >= @DATES AND [stats_Date] <= @DATEE
GROUP BY [stats_Date]
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MERGE INTO [MSADailyStats] [MSA]
USING
(
SELECT [stats_Date],SUM([Trans_Value]) AS [Trans_Value]
FROM [Transaction_Statistics]
WHERE [stats_Date] >= @DATES AND [stats_Date] <= @DATEE AND [TransCodeID] IN (9000149,9000156,9000151,9000152,9000153,9000156,9000181,9000182,9000183,9000184,9000185,9000186)
GROUP BY [stats_Date]
) [CMS]
ON [MSA].[DATE] = [CMS].[stats_Date]
WHEN MATCHED THEN
UPDATE SET [Revenue] = [CMS].[Trans_Value];
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MERGE INTO [MSADailyStats] [MSA]
USING
(
SELECT [booked_Date],COUNT([RoomID]) AS [RoomID]
FROM [Rooms_Booked]
WHERE [OOOId] > 1 AND [booked_Date] >= @DATES AND [booked_Date] <= @DATEE
GROUP BY [booked_Date]
) [CMS]
ON [MSA].[DATE] = [CMS].[booked_Date]
WHEN MATCHED THEN
UPDATE SET [OOO] = [CMS].[RoomID];
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT *
FROM [MSADailyStats]
ORDER BY [Date]
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE [MSADailyStats]
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
