June 3, 2010 at 8:28 pm
I have the following query. I need to combine the subquery for the current month along with the main query to get the last 3 months totals.
Can someone assist? I get an error that says,
Msg 245, Level 16, State 1, Line 25
Conversion failed when converting the nvarchar value '
SELECT
DataTable.*, (SELECT AVG(InclGST)
FROM Mobile
WHERE Month = April
AND Year = ' to data type int.
--Query
DECLARE @CurrMonth VARCHAR(20)
DECLARE @CurrYear INT
DECLARE @PivotSQL NVARCHAR(MAX)
DECLARE @PivotColumns NVARCHAR(MAX)
DECLARE @i SMALLINT = -2
SET @CurrMonth = (SELECT CurrentMonth FROM CurrentMonth)
SET @CurrYear = YEAR(GETDATE())
SET @PivotColumns = N''
WHILE @i <= 0
BEGIN
SELECT @PivotColumns = @PivotColumns
+ '['
+ DATENAME(month, DATEADD(mm,@i -1,GETDATE()))
+ SPACE(1)
+ CAST(YEAR(DATEADD(mm,@i,GETDATE())) AS VARCHAR(4))
+ ']'
+ CASE WHEN @i < 0 THEN ', ' ELSE '' END;
SET @i = @i + 1;
END
SET @PivotSQL = N'
SELECT
DataTable.*, (SELECT AVG(InclGST)
FROM Mobile
WHERE Month = ' + @CurrMonth + '
AND Year = ' + @CurrYear +') AS CurrMonthAvg
FROM
(
SELECT InclGST, YearMonth = Month + SPACE(1) + CAST(Year AS VARCHAR(4))
FROM Mobile
) DataTable
PIVOT
(
AVG(InclGST)
FOR YearMonth
IN ( ' +
@PivotColumns + '
)
) PivotTable'
EXEC SP_EXECUTESQL @PivotSQL
June 3, 2010 at 9:54 pm
Your block of code:
SET @PivotSQL = N'
SELECT
DataTable.*, (SELECT AVG(InclGST)
FROM Mobile
WHERE Month = ' + @CurrMonth + '
AND Year = ' + @CurrYear +') AS CurrMonthAvg
FROM
Needs to be:
SET @PivotSQL = N'
SELECT
DataTable.*, (SELECT AVG(InclGST)
FROM Mobile
WHERE Month = ' + QuoteName(@CurrMonth, char(39)) + '
AND Year = ' +convert(char(4), @CurrYear) +') AS CurrMonthAvg
FROM
Since you didn't convert @CurrYear from INT to CHAR, the optimizer is trying to convert the string:
SELECT
DataTable.*, (SELECT AVG(InclGST)
FROM Mobile
WHERE Month = April
AND Year = '
to an INT, which is obviously failing.
And, unless you have columns with the month names, you'll need to put the month name (@CurrMonth) in quotes also.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 10:11 pm
Hi WayneS,
Here is the modified query, but i get an error,
Msg 107, Level 15, State 1, Line 3
The column prefix 'DataTable' does not match with a table name or alias name used in the query.
DECLARE @CurrMonth VARCHAR(20)
DECLARE @CurrYear INT
DECLARE @PivotSQL NVARCHAR(MAX)
DECLARE @PivotColumns NVARCHAR(MAX)
DECLARE @i SMALLINT = -2
SET @CurrMonth = (SELECT CurrentMonth FROM CurrentMonth)
SET @CurrYear = YEAR(GETDATE())
SET @PivotColumns = N''
SET @PivotColumns = N''
WHILE @i <= 0
BEGIN
SELECT @PivotColumns = @PivotColumns
+ '['
+ DATENAME(month, DATEADD(mm,@i -1,GETDATE()))
+ SPACE(1)
+ CAST(YEAR(DATEADD(mm,@i,GETDATE())) AS VARCHAR(4))
+ ']'
+ CASE WHEN @i < 0 THEN ', ' ELSE '' END;
SET @i = @i + 1;
END
SET @PivotSQL = N'
SELECT
DataTable.*, (SELECT AVG(InclGST)
FROM Mobile
WHERE Month = ' + QuoteName(@CurrMonth, CHAR(39)) + '
AND Year = ' + CONVERT(CHAR(4), @CurrYear) +') AS CurrMonthAvg
FROM
(
SELECT CostCentre, InclGST,
YearMonth = Month + SPACE(1) + CAST(Year AS VARCHAR(4))
FROM Mobile
) DataTable
PIVOT
(
AVG(InclGST)
FOR YearMonth
IN ( ' +
@PivotColumns + '
)
) PivotTable'
EXEC SP_EXECUTESQL @PivotSQL
June 3, 2010 at 10:21 pm
Right before: EXEC SP_EXECUTESQL @PivotSQL
Put a "PRINT @PivotSQL" statement, then post the results.
Actually, if you look at it, I'll bet you can see the problem.
Another tip... if you add a "@Debug tinyint=0" parameter to your procedure, and then do the print as:
if @Debug > 0 PRINT @PivotSQL
You'll be able to add some debugging code to your procedure. Just call the procedure by setting @Debug > 0 to get it to print the code.
I use a tinyint so that I can have levels of debugging - none, up to whatever level I desire.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 10:30 pm
I don't get it!
I removed DataTable.* with just * and it works
The working query now looks like this,
DECLARE @CurrMonth VARCHAR(20)
DECLARE @CurrYear INT
DECLARE @PivotSQL NVARCHAR(MAX)
DECLARE @PivotColumns NVARCHAR(MAX)
DECLARE @i SMALLINT = -2
SET @CurrMonth = (SELECT CurrentMonth FROM CurrentMonth)
SET @CurrYear = YEAR(GETDATE())
SET @PivotColumns = N''
SET @PivotColumns = N''
WHILE @i <= 0
BEGIN
SELECT @PivotColumns = @PivotColumns
+ '['
+ DATENAME(month, DATEADD(mm,@i -1,GETDATE()))
+ SPACE(1)
+ CAST(YEAR(DATEADD(mm,@i,GETDATE())) AS VARCHAR(4))
+ ']'
+ CASE WHEN @i < 0 THEN ', ' ELSE '' END;
SET @i = @i + 1;
END
SET @PivotSQL = N'
SELECT
*, (SELECT AVG(InclGST)
FROM Mobile
WHERE Month = ' + QuoteName(@CurrMonth, CHAR(39)) + '
AND Year = ' + CONVERT(CHAR(4), @CurrYear) +') AS CurrMonthAvg
FROM
(SELECT CostCentre, InclGST,
YearMonth = Month + SPACE(1) + CAST(Year AS VARCHAR(4))
FROM Mobile) DataTable
PIVOT
(
AVG(InclGST)
FOR YearMonth
IN ( ' +
@PivotColumns + ')) PivotTable'
PRINT @PivotSQL
EXEC SP_EXECUTESQL @PivotSQL
June 3, 2010 at 10:39 pm
I bet if you put PivotTable in there, it would work.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 10:48 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply