Update Formula - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Formula

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-11-10 : 10:46:38

I have this formula that is working great. I us this to get my numbers for the week. The only time it doesn't work is when a new week begins and there is no data yet.Here is the error message I am getting.....
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 8134, Level 16, State 1, Procedure p_All_Nego_Non_Claims_WMY, Line 141
Divide by zero error encountered.
The statement has been terminated.

Here is my update formula the problem is where my division is in yellow. How can I fix this so this does not show up. Thanks.

DECLARE @NegotiatedCountW int
SET @NegotiatedCountW = (SELECT COUNT(*) FROM IMPACT_PROD.DBO.vw_Claims_Settlement_All_Claims_Negotiated_Non_Negotiated1_of_2 t3
Inner Join sumALLNegoNonNegoClaimsWMY t4 on t3.clm_id1 = t4.clmNumber or t4.type = 'W' and t3.clm_nego <>0.00 and clm_nego <>0.00 and
(YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))Where type = 'W')

UPDATE [sumALLNegoNonNegoClaimsWMY]
SET SuccessRatio = @NegotiatedCountW / ([TotalNumberCLMs]*1.0) * 100 Where type = 'W'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-10 : 10:50:49

Use Nullif in denominator.

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-11-10 : 13:33:56

How would I do that for a declare statement?

DECLARE @NegotiatedCountW int
SET @NegotiatedCountW = (SELECT COUNT(*) FROM IMPACT_PROD.DBO.vw_Claims_Settlement_All_Claims_Negotiated_Non_Negotiated1_of_2 t3
Inner Join sumALLNegoNonNegoClaimsWMY t4 on t3.clm_id1 = t4.clmNumber or t4.type = 'W' and t3.clm_nego <>0.00 and clm_nego <>0.00 and
(YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))Where type = 'W')

UPDATE [sumALLNegoNonNegoClaimsWMY]
SET SuccessRatio = @NegotiatedCountW / ([TotalNumberCLMs]*1.0) * 100 Where type = 'W'

quote:
Originally posted by sodeep

Use Nullif in denominator.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-10 : 14:39:55

[code]UPDATE sumALLNegoNonNegoClaimsWMY
SET SuccessRatio = 100.0E * @NegotiatedCountW / TotalNumberCLMs
WHERE type = 'W'
AND TotalNumberCLMs > 0[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources