When inserting data from a query I get this error message:
select count(distinct [Id]) as [count], count(*) as [totalcount] from [dbo].[MonthlyClubPaymentBasis2].[0] where [Id] is not null - Reference to database and/or server name in 'dbo.MonthlyClubPaymentBasis2.0' is not supported in this version of SQL Server.
I have tried with and without the ID column but still receives 422 unprocessable entity error...
The target table definition below:
CREATE TABLE [dbo].[MonthlyClubPaymentBasis2.0] (
[ID] INT NOT NULL,
[GRASSROOT_CLUB_ID] NVARCHAR (50) NULL,
[PERIOD] NVARCHAR (50) NULL,
[GROSS_TRANSACTIONS] FLOAT (53) NULL,
[NET_TRANSACTIONS] FLOAT (53) NULL,
[CLUB_SUPPORT_AMOUNT] FLOAT (53) NULL,
[PROCESSED_DATE] DATETIME NULL,
CONSTRAINT [PK_MonthlyClubPaymentBasis2.0] PRIMARY KEY CLUSTERED ([ID] ASC)
);
Query:
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID,
[CLUB_ID] AS GRASSROOT_CLUB_ID,
FORMAT([REGISTERED_DATE], 'yyyy-MM') AS PERIOD,
ROUND(SUM(TRANSACTION_PRICE), 2) AS GROSS_TRANSACTIONS,
ROUND(SUM([TRANSACTION_PRICE] * 0.8), 2) AS NET_TRANSACTIONS,
ROUND(SUM([TRANSACTION_PRICE] * 0.2), 2) AS CLUB_SUPPORT_AMOUNT,
CONVERT(DATE, GETDATE()) AS [PROCESSED_DATE]
FROM [data_model].[tv2_order]
WHERE [REGISTERED_DATE] BETWEEN '2023-01-01' AND '2024-01-01'
GROUP BY [CLUB_ID], FORMAT([REGISTERED_DATE], 'yyyy-MM');
Any ideas or suggestion?
