'Division by zero' error

Why does this statement return a "division by zero" error?

INSERT INTO #finalRevenue
SELECT t1.glBranch, t1.glBranchDesc, t1.avgWeekRev, t2.avgBaselineRev, Sum( t1.avgWeekRev - t2.avgBaselineRev) As revVarience,'percVarience' =
CASE
WHEN t2.avgBaselineRev = 0 THEN 0
ELSE Sum((t1.avgWeekRev - t2.avgBaselineRev) / t2.avgBaselineRev ) * 100
END
,t1.Entity
FROM #Revenue1 t1, #Revenue2 t2
WHERE t1.GLBranch = t2.GLBranch
GROUP BY t1.glBranch, t1.glBranchDesc, t1.avgWeekRev, t2.avgBaselineRev, t1.Entity

A select of t2.avgBaselineRev in Query Analyzer returns .0000, so it is not NULL.

Comments

  • : Why does this statement return a "division by zero" error?
    :
    : INSERT INTO #finalRevenue
    : SELECT t1.glBranch, t1.glBranchDesc, t1.avgWeekRev, t2.avgBaselineRev, Sum( t1.avgWeekRev - t2.avgBaselineRev) As revVarience,'percVarience' =
    : CASE
    : WHEN t2.avgBaselineRev = 0 THEN 0
    : ELSE Sum((t1.avgWeekRev - t2.avgBaselineRev) / t2.avgBaselineRev ) * 100
    : END
    : ,t1.Entity
    : FROM #Revenue1 t1, #Revenue2 t2
    : WHERE t1.GLBranch = t2.GLBranch
    : GROUP BY t1.glBranch, t1.glBranchDesc, t1.avgWeekRev, t2.avgBaselineRev, t1.Entity
    :
    : A select of t2.avgBaselineRev in Query Analyzer returns .0000, so it is not NULL.
    :


    .0000 is zero (0.0000), so it will not work.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion