null values in pivot with dynamic sql

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)

null values in pivot with dynamic sql

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-06-16 : 13:00:19

I am trying to get rid of nulls from % Used SUM calculations but I cannot for some reason. Basically I can have nulls (no data) for some of the volumes for a specific date. I would like to print '0' value in this case. I tried CASE statement, coealsece and isnull with no success.

ALTER PROCEDURE [dbo].[sp_SYS8912_capacityByWeek_REV3]

AS
BEGIN
SET NOCOUNT ON;
DECLARE @YrWkList varchar(1000),@sql varchar(4000)
select @YrWkList = stuff((select distinct ','+ QUOTENAME(convert(varchar, RUNDATE, 101)) from dbo.SYS8912_HIST

WHERE TYPE ='VOLUME'
and
RUNDATE >='2012-01-01'

order by ','+ QUOTENAME(convert(varchar, RUNDATE, 101)) DESC for xml path('')),1,1,'')

set @sql='select *
from
(
select LOCATION_DESC, Fullname, CASE WHEN [% Used] is NOT NULL THEN (CAST([% Used] AS numeric (18,2))) ELSE 0 END as [% Used], convert(varchar, RUNDATE, 101) as RUNDATE from dbo.SYS8912_HIST
WHERE TYPE =''VOLUME''

and
RUNDATE >=''2012-01-01''
group by LOCATION_DESC,Fullname, RUNDATE,CASE WHEN [% Used] is NOT NULL THEN (CAST([% Used] AS numeric (18,2))) ELSE 0 END

)m
pivot (max([% Used]) for RUNDATE in (' + @YrWkList + '))p'

exec (@sql)

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 11:34:59

replace last set exec with below code


set @sql='select LOCATION_DESC,Fullname, COALESCE(' + REPLACE(@YrWkList,',','),COALESCE(') + ')
from
(
select LOCATION_DESC, Fullname, CASE WHEN [% Used] is NOT NULL THEN (CAST([% Used] AS numeric (18,2))) ELSE 0 END as [% Used], convert(varchar, RUNDATE, 101) as RUNDATE from dbo.SYS8912_HIST
WHERE TYPE =''VOLUME''

and 
RUNDATE >=''2012-01-01''
group by LOCATION_DESC,Fullname, RUNDATE,CASE WHEN [% Used] is NOT NULL THEN (CAST([% Used] AS numeric (18,2))) ELSE 0 END

)m
pivot (max([% Used]) for RUNDATE in (' + @YrWkList + '))p'

exec (@sql)

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

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-06-19 : 09:25:51

Getiing this error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'm'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 15:20:29

[code]
set @sql='select LOCATION_DESC,Fullname, COALESCE(' + REPLACE(@YrWkList,',',',0),COALESCE(') + ',0)
from
(
select LOCATION_DESC, Fullname, CASE WHEN [% Used] is NOT NULL THEN (CAST([% Used] AS numeric (18,2))) ELSE 0 END as [% Used], convert(varchar, RUNDATE, 101) as RUNDATE from dbo.SYS8912_HIST
WHERE TYPE =''VOLUME''

and
RUNDATE >=''2012-01-01''
group by LOCATION_DESC,Fullname, RUNDATE,CASE WHEN [% Used] is NOT NULL THEN (CAST([% Used] AS numeric (18,2))) ELSE 0 END

)m
pivot (max([% Used]) for RUNDATE in (' + @YrWkList + '))p'

exec (@sql)
[/code]

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

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-06-19 : 16:32:24

Thank you Visakh16, still one more problem...

nulls are eliminated now but I lost column names (headers).....
They are supposed to display weekly dates ex, 06/14/2012 etc....

LOCATION_DESC Fullname (No column name) (No column name) (No column name)
Colorado, TGS SERVER1:/534AL22_Ar_VOL6 0.00 0.00 0.00
Colorado, TGS SERVER1:/534AL22_terfe 0.07 0.07 0.07
Colorado, TGS SERVER1:/534AL22_VOL11 55.25 55.35 54.88
Colorado, TGS SERVER1:/534AL22_VOL13 85.10 85.05 84.89

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 18:52:52

the only other way is to use two lists


ALTER PROCEDURE [dbo].[sp_SYS8912_capacityByWeek_REV3]

AS
BEGIN
SET NOCOUNT ON;
DECLARE @YrWkList varchar(1000),@YrWkList1 varchar(1000),@sql varchar(4000)
select @YrWkList = stuff((select distinct ','+ QUOTENAME(convert(varchar, RUNDATE, 101)) from dbo.SYS8912_HIST 
select @YrWkList1 = stuff((select distinct ',COALESCE('+ QUOTENAME(convert(varchar, RUNDATE, 101)) + ',0) AS [' + QUOTENAME(convert(varchar, RUNDATE, 101)) + ']' from dbo.SYS8912_HIST 

WHERE TYPE ='VOLUME'
and
RUNDATE >='2012-01-01'

order by ','+ QUOTENAME(convert(varchar, RUNDATE, 101)) DESC for xml path('')),1,1,'') 


set @sql='select LOCATION_DESC,Fullname,' + @YrWkList1 + '
from
(
select LOCATION_DESC, Fullname, CASE WHEN [% Used] is NOT NULL THEN (CAST([% Used] AS numeric (18,2))) ELSE 0 END as [% Used], convert(varchar, RUNDATE, 101) as RUNDATE from dbo.SYS8912_HIST
WHERE TYPE =''VOLUME''

and 
RUNDATE >=''2012-01-01''
group by LOCATION_DESC,Fullname, RUNDATE,CASE WHEN [% Used] is NOT NULL THEN (CAST([% Used] AS numeric (18,2))) ELSE 0 END

)m
pivot (max([% Used]) for RUNDATE in (' + @YrWkList + '))p'

exec (@sql)

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-06-20 : 08:59:21

Use unambigious date formats
http://beyondrelational.com/modules/2/blogs/70/posts/10899/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-06-22 : 16:05:51

Thank you visakh16 it works now

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 22:53:57

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources