February 27, 2018 at 8:30 am
Hi Guys,
I have the following code but for some reason Power BI doesn't seem to accept declare. Does anyone know a way around this? The error message I get in Power BI is:
Query1 Microsoft SQL: Incorrect syntax near the keyword 'DECLARE'
Many thanks
DECLARE @sd DATETIME, @ed DATETIME;
-- set the start date to the first day of this month
SET @sd = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
-- if it's the first, we want last month instead
IF DATEPART(DAY, GETDATE()) = 1
BEGIN
SET @sd = DATEADD(MONTH, -1, @sd);
END
ELSE
-- set end dates
IF DATEPART(DAY, GETDATE()) = 1
SET @ed = DATEADD(MONTH, 1, @sd)
ELSE
SET @ed = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
SELECT logfile.Dealer,Dealers.Name
,COUNT(*) AS Booked
, SUM(CASE RepairCodes.[Service] WHEN 'Y' THEN 1 ELSE 0 END) AS SER
, SUM(CASE ServCode WHEN 'MST' THEN 1 WHEN 'S&M' THEN 1 ELSE 0 END) AS MST
, SUM(CASE ServCode WHEN 'MOT' THEN 1 ELSE 0 END) AS MOT
, SUM(CASE ServCode WHEN 'WAR' THEN 1 ELSE 0 END) AS WAR
, SUM(CASE PayType WHEN 'INTERNAL' THEN 1 ELSE 0 END) AS [INT]
,COUNT(DISTINCT RegNo) AS Vehs
,COUNT(*)
-SUM(CASE RepairCodes.[Service] WHEN 'Y' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'MST' THEN 1 WHEN 'S&M' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'MOT' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'WAR' THEN 1 ELSE 0 END) AS Other
, SUM(TotalValue) AS [Value]
FROM LogFile
LEFT OUTER JOIN RepairCodes
ON RepairCode = ServCode
AND LogFile.Dealer = RepairCodes.Dealer
JOIN Dealers ON Dealers.Dealer=LogFile.Dealer
AND dbo.LogFile.Created >= @sd
AND dbo.LogFile.Created < @ed
AND DBO.LogFile.Tran1 in ( 'IBB','W3B')
GROUP BY LogFile.Dealer, Dealers.Name
order by Dealers.Name
February 27, 2018 at 10:26 am
I tried to reproduce your problem by using DECLARE inside the query window in PowerBI (after you specify the database etc), and mine worked. Granted, my SQL was a lot simpler... so it's not the DECLARE that's causing the problem. I executed this inside that query window in PowerBi:
DECLARE @NumRecords INT
SET @NumRecords = 1+ABS(CHECKSUM(NEWID())%10);
SELECT TOP (@NumRecords) *
FROM dbo.SymptomData;
and mine worked, so there's something else going on in there. What happens if you execute your T-SQL inside SSMS? It works there, right?.
February 28, 2018 at 2:10 am
Thanks for coming back to me. Yeah, its works fine in SSMS and also I import the query into excel and it also works fine.
February 28, 2018 at 3:25 am
Quick question, have you tried separating the declare into two lines?
😎
DECLARE @sd DATETIME;
DECLARE @ed DATETIME;
February 28, 2018 at 8:36 am
Amazing Eirikur Eiriksson, it now works fine thank you so much
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply