CODEWITHSUNDEEP
sqlsql-serverssms
StayPuft
StayPuft

Reputation: 135

Divide By Zero Issue

I'm currently encountering an issue with dividing by zero - I'm trying to implement CASE to just mark the divided number as "1" however, I'm still getting the error - I'm guessing I'm missing something here? Just not sure what...

SELECT
    CASE WHEN c.PageCount IS NULL OR c.PageCount=0 THEN 1 ELSE c.PageCount END as [PageCount], 

    cast(c.PageCount / CASE WHEN DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) IS NULL OR DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) = 0 THEN 1 ELSE DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate)END as decimal(5,2)) as PagesPerMinute,

    CASE
       WHEN DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) >= 30 AND CAST(DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) AS FLOAT)/CAST([PageCount] AS FLOAT) >= 1 THEN 
             CASE WHEN CAST([PageCount] AS FLOAT) * 0.5 /*Half minute*/ > 45 /*minutes*/ THEN 45 ELSE CAST([PageCount] AS FLOAT) * 0.5 /*Half minute*/ END 
       WHEN DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) >= 60 AND CAST(DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) AS FLOAT)/CAST([PageCount] AS FLOAT) < 1 /* 1 minute/page */ THEN 60
       ELSE
         DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate)
    END    [MinutesPaid]


FROM 
    tbl_Charts c
    left outer join (select cd.ChartId, count(*) as CodesFound from tbl_ChartCodes cd group by cd.ChartId) cd on cd.ChartId = c.ChartId,
    tbl_WFChartEvents ev, 
    tbl_Users u,
    (select evar.ChartId, evar.ActionUserId, ar.ResultDescription, evar.ActionRemark, evar.ActionDate
    from tbl_WFChartEventActions evar, tbl_WFChartEventActionResults ar 
    where evar.EventId = 201 
    and evar.ActionResultId = ar.ResultID and evar.EventId = ar.EventID 
    and evar.ActionTypeId = ar.ActionTypeID
    and evar.EventId = 201) arr 

WHERE 
c.ChartId = ev.ChartId
and ev.EventId = 201
and ev.EventCreateUserId = u.UserId
and arr.ActionUserId = u.UserId
and arr.ChartId = c.ChartId

order by ev.EventCreateDate

Upvotes: 0

Views: 785

Answers (3)

Anon
Anon

Reputation: 10908

For easier readability/maintainability of division formulas, wrap every divisor with NULLIF(x,0).

This is easier than checking for zeros with a case statement and ensuring the divisor is identical to the case predicate. Everywhere you see a / [...], make it / NULLIF( [...] ,0)

instead of this:

cast(
  c.PageCount / CASE
                  WHEN (DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) IS NULL 
                     OR DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) = 0
                  )
                  THEN 1
                  ELSE DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate)
                END
  as decimal(5,2)
) as PagesPerMinute,

do this:

cast(
  coalesce(
    c.PageCount / NULLIF(DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate), 0)
   ,c.PageCount --Return this value if preceding is null or div_by_zero
  )
  as as decimal(5,2)
) as PagesPerMinute

Upvotes: 1

bowlturner
bowlturner

Reputation: 2016

OK, 3rd time the charm? I thought I found the problem twice, but I think I know what's going on now.

All sql I know, you can't use an aliased column else where in the select. By aliasing c.PageCount to [PageCount] I believe you are still using the original c.PageCount without the table alias. To test this out, just change [PageCount] to [PageCount2]. I suspect you will get an error it doesn't recognize the column name.

This only 'appears' to work because you reused the already unique column name from the results.

What I meant was take this case and replace the [PageCount] with it.

CASE WHEN c.PageCount IS NULL OR c.PageCount=0 THEN 1 ELSE c.PageCount END

like this

CASE
   WHEN DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) >= 30 AND CAST(DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) AS FLOAT)/CAST(CASE WHEN c.PageCount IS NULL OR c.PageCount=0 THEN 1 ELSE c.PageCount END AS FLOAT) >= 1 THEN 
         CASE WHEN CAST([PageCount] AS FLOAT) * 0.5 /*Half minute*/ > 45 /*minutes*/ THEN 45 ELSE CAST([PageCount] AS FLOAT) * 0.5 /*Half minute*/ END 
   WHEN DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) >= 60 AND CAST(DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) AS FLOAT)/CAST(CASE WHEN c.PageCount IS NULL OR c.PageCount=0 THEN 1 ELSE c.PageCount END AS FLOAT) < 1 /* 1 minute/page */ THEN 60
   ELSE
     DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate)
END    [MinutesPaid]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I think your problem is in this case statement:

CASE WHEN DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) >= 30 AND
          CAST(DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) AS FLOAT)/CAST([PageCount] AS FLOAT) >= 1
     THEN (CASE WHEN CAST([PageCount] AS FLOAT) * 0.5 /*Half minute*/ > 45 /*minutes*/
                THEN 45 ELSE CAST([PageCount] AS FLOAT) * 0.5 /*Half minute*/
           END)
     WHEN DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) >= 60 AND CAST(DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) AS FLOAT)/CAST([PageCount] AS FLOAT) < 1 /* 1 minute/page */
     THEN 60
     ELSE DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate)
END AS [MinutesPaid]

The first when statement has a divisision. Just rephrase this as:

CASE WHEN DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) >= 30 AND
          CAST([PageCount] AS FLOAT) > 0 AND
          CAST(DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) AS FLOAT) >= CAST([PageCount] AS FLOAT)
     THEN (CASE WHEN CAST([PageCount] AS FLOAT) * 0.5 /*Half minute*/ > 45 /*minutes*/
                THEN 45 ELSE CAST([PageCount] AS FLOAT) * 0.5 /*Half minute*/
           END)
     WHEN DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) >= 60 AND CAST(DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate) AS FLOAT)/CAST([PageCount] AS FLOAT) < 1 /* 1 minute/page */
     THEN 60
     ELSE DATEDIFF(mi, ev.EventCreateDate, ev.EventCompletionDate)
END AS [MinutesPaid]

I removed the division with a simple comparison and guarantee the value is not 0.

Upvotes: 0

Related Questions