calculating avg in pvt - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

calculating avg in pvt

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-13 : 11:18:22

I am getting the following error on this query:
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '('.

Before that I was getting conversion error as well.

Out of 30K recs, DURATION_MIN contains 117 nulls, DURATION_MIN is set us float. I would like to show avg number up to 2 decimal points.

ALTER PROCEDURE [dbo].[sp_TEST_TTR]

AS
BEGIN
SET NOCOUNT ON;
DECLARE @YrWkList varchar(1000),@sql varchar(4000)
select @YrWkList = stuff((select distinct ','+ QUOTENAME(OPEN_MONTH) from dbo.tblYTD order by ','+ QUOTENAME(OPEN_MONTH) for xml path('')),1,1,'')

set @sql='select *
from
(
select IMPACT_LEVEL,IMPACT_LEVEL AS IMPACT, Team, SLEVEL, OPEN_MONTH, DURATION_MIN FROM dbo.tblYTD
WHERE IMPACT_LEVEL like ''P1%''
group by IMPACT_LEVEL, Team, SLEVEL,OPEN_MONTH ,NUMBERPRGN1,DURATION_MIN
)m
pivot (
AVG( CAST(COALESCE(DURATION_MIN/60,0) AS decimal) ) for OPEN_MONTH in (' + @YrWkList + '))p'

exec (@sql)

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:43:22

replace exec(@sql) with print(@sql) and post the resultant query first

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-13 : 12:04:12

select *
from
(
select IMPACT_LEVEL,IMPACT_LEVEL AS IMPACT, Team, SLEVEL, OPEN_MONTH, DURATION_MIN FROM dbo.tblYTD
WHERE IMPACT_LEVEL like 'P1%'
group by IMPACT_LEVEL, Team, SLEVEL,OPEN_MONTH ,NUMBERPRGN1,DURATION_MIN
)m
pivot (
AVG( CAST(COALESCE(DURATION_MIN/60,0) AS decimal) ) for OPEN_MONTH in ([APRIL ],[FEBRUARY ],[JANUARY ],[MARCH ]))p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 12:14:55

did you try running this query separately and see if its working fine? I cant spot any obvious errors on it!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-13 : 12:23:24

You can't do any arithmetic operations in the pivot clause.

AVG( ~~CAST(COALESCE(DURATION_MIN/60,0) AS decimal)~~ m.mycalculatedfield )

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 12:25:51

ah...missed the obvious...i was looking at overall syntax didnt look inside aggregation part!

why not do it like this


select *
from
(
select IMPACT_LEVEL,IMPACT_LEVEL AS IMPACT, Team, SLEVEL, OPEN_MONTH, CAST(COALESCE(DURATION_MIN/60,0) AS decimal) AS DURATION_MIN FROM dbo.tblYTD
WHERE IMPACT_LEVEL like 'P1%'
group by IMPACT_LEVEL, Team, SLEVEL,OPEN_MONTH ,NUMBERPRGN1,DURATION_MIN
)m
pivot ( 
AVG(DURATION_MIN) for OPEN_MONTH in ([APRIL ],[FEBRUARY ],[JANUARY ],[MARCH ]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-13 : 13:57:08

Thank you both. It works now. Did not realize I cannot do arithemetic in pvt.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 15:10:54

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources