| Author |
 Topic  |
|
|
nietzky
Yak Posting Veteran
63 Posts |
 Posted - 06/16/2012 : 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
India
48076 Posts |
Posted - 06/17/2012 : 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
63 Posts |
Posted - 06/19/2012 : 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
India
48076 Posts |
Posted - 06/19/2012 : 15:20:29
|
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)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
nietzky
Yak Posting Veteran
63 Posts |
Posted - 06/19/2012 : 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 |
Edited by - nietzky on 06/19/2012 16:33:55 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 06/19/2012 : 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/
|
Edited by - visakh16 on 06/19/2012 18:53:18 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
|
|
nietzky
Yak Posting Veteran
63 Posts |
Posted - 06/22/2012 : 16:05:51
|
| Thank you visakh16 it works now |
Edited by - nietzky on 06/22/2012 16:06:27 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 06/22/2012 : 22:53:57
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
null values in pivot with dynamic sql
