November 8, 2012 at 5:15 pm
I have a table with product, month and revenue data.
But for some products, there is no data if the product was introduced later on.
I need to pivot that data but I am getting divide by zero error when I pivot. The error is at FOR MO_NBR line in pivot section.
How do I fill in the missing data? Or go around it?
SELECT prod,
MO_NBR,
sum(cast(Revenue as float)) as TotalRevenue
INTO #tblMom
FROM tbl_data
GROUP BY prod, MO_NBR
ORDER BY prod, MO_NBR
DECLARE @cols VARCHAR(8000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + MO_NBR
FROM #tblMom AS t2
ORDER BY '],[' + MO_NBR
FOR XML PATH('')), 1, 2, '') + ']'
DECLARE @query VARCHAR(8000)
SET @query = '
SELECT *
INTO tmpMomProd
FROM #tblMom
PIVOT
(
sum([TotalRevenue])
FOR MO_NBR
IN (' + @cols + ')
)
AS p '
EXECUTE(@query)
select prod
, case when [2011_01] is null or [2011_02] is null then 0 else ( [2011_02] / [2011_01] ) end as Change_201102_201101
, case when [2011_02] is null or [2011_03] is null then 0 else ( [2011_03] / [2011_02] ) end as Change_201103_201102
, case when [2011_03] is null or [2011_04] is null then 0 else ( [2011_04] / [2011_03] ) end as Change_201104_201103
, case when [2011_04] is null or [2011_05] is null then 0 else ( [2011_05] / [2011_04] ) end as Change_201105_201104
, case when [2011_05] is null or [2011_06] is null then 0 else ( [2011_06] / [2011_05] ) end as Change_201106_201105
, case when [2011_06] is null or [2011_07] is null then 0 else ( [2011_07] / [2011_06] ) end as Change_201107_201106
, case when [2011_07] is null or [2011_08] is null then 0 else ( [2011_08] / [2011_07] ) end as Change_201108_201107
, case when [2011_08] is null or [2011_09] is null then 0 else ( [2011_09] / [2011_08] ) end as Change_201109_201108
, case when [2011_09] is null or [2011_10] is null then 0 else ( [2011_10] / [2011_09] ) end as Change_201110_201109
, case when [2011_10] is null or [2011_11] is null then 0 else ( [2011_11] / [2011_10] ) end as Change_201111_201110
, case when [2011_11] is null or [2011_12] is null then 0 else ( [2011_12] / [2011_11] ) end as Change_201112_201111
, case when [2011_12] is null or [2012_01] is null then 0 else ( [2012_01] / [2011_12] ) end as Change_201201_201112
, case when [2012_01] is null or [2012_02] is null then 0 else ( [2012_02] / [2012_01] ) end as Change_201202_201201
, case when [2011_02] is null or [2012_03] is null then 0 else ( [2012_03] / [2012_02] ) end as Change_201203_201202
, case when [2011_03] is null or [2012_04] is null then 0 else ( [2012_04] / [2012_03] ) end as Change_201204_201203
, case when [2011_04] is null or [2012_05] is null then 0 else ( [2012_05] / [2012_04] ) end as Change_201205_201204
, case when [2011_05] is null or [2012_06] is null then 0 else ( [2012_06] / [2012_05] ) end as Change_201206_201205
, case when [2011_06] is null or [2012_07] is null then 0 else ( [2012_07] / [2012_06] ) end as Change_201207_201206
, case when [2011_07] is null or [2012_08] is null then 0 else ( [2012_08] / [2012_07] ) end as Change_201208_201207
, case when [2011_08] is null or [2012_09] is null then 0 else ( [2012_09] / [2012_08] ) end as Change_201209_201208
into tmpMomProdChange
from tmpMomProd
November 8, 2012 at 5:33 pm
I don't see anywhere in your query where you're actually doing division. Is this a short version of the code or is it end to end?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 8, 2012 at 7:31 pm
I have another query that does division month over month. I do a case there to check if either numerator or denominator is zero, then zero else division. But the error comes at the statement I mentioned in the original post.
I have updated the query to reflect the division.
Thanks.
November 9, 2012 at 1:21 am
I went around by adding the following:
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply