November 14, 2013 at 7:03 am
Hi,
I'm just trying to get a "percent of overtime" from the query below:
I get a 'divide by zero error'
I need to dive the 'total_pr' by the 'total_ot' then multiply by 100 to get the percentage.
Maybe I haven't had enough caffeine this morning..
, TOTAL_PR/OT_Hrs + OT_amt + OT_shift * 100 as [Percent of Overtime], TOTAL_PR, PayDate,Org2CodeDesc
FROM (
Select fullname ,pehempno,pehearncode,ERNSTUBDESC
,pehratefactor
,case
when pehearncode in ('0002','0022','0025','0066','0104','AI02','N002','N104')
then pehcurhrs else 0
end AS OT_Hrs
,case
when pehearncode in ('0002','0022','0025','0066','0104','AI02','0066','0104','N002','N104')
then pehcuramt else 0
end AS OT_Amt
,case
when pehearncode in ('0002','0022','0025','0066','0104','AI02','0066','0104','N002','N104')
then PehShfShiftAmt else 0
end AS OT_Shift
,pehcuramt AS TOTAL_PR
,pehpaydate AS PayDate
,RTRIM(PEHORGLVL2) + ' - ' + DBO.WSI_F_GET_ORGDESC(PEHORGLVL2,2) AS Org2CodeDesc
from wsi_v_personnel_data
inner join pearhist on eeid = peheeid
INNER JOIN ORGLEVEL ON PEHORGLVL2 = ORGCODE AND ORGLVL = 2
INNER JOIN EARNCODE ON PEHEARNCODE = ERNEARNCODE
--where pehorglvl2 = 'AGIKEC'
and pehpaydate >= '01/01/2013'
--ORDER BY PEHORGLVL2 ,fullname,PEHEMPNO
) X]
November 14, 2013 at 7:43 am
Do you remember basic order of operations from math class?
You have this.
OTAL_PR / OT_Hrs + OT_amt + OT_shift * 100
Since you didn't use any parenthesis this is nothing like what you said you wanted as a calculation.
Try something like this.
OTAL_PR / ((OT_Hrs + OT_amt + OT_shift) * 100)
No that won't help with the divide by zero issue because you can have 0 for OT_Hrs and OT_amt and OT_shift.
Something like this MIGHT be correct.
OTAL_PR / (nullif(OT_Hrs + OT_amt + OT_shift, 0) * 100)
This will return a NULL if the sum of those three is 0 which will result in the entire calculation being NULL. Without any more details to work with that is my best guess.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2013 at 7:48 am
Yep using NULL worked. THANKS!
November 14, 2013 at 8:05 am
You're welcome. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply