Divide by zero problem

[Crystalguru]

MS SQL Server 2000
Using Query Analyzer to create query

The below query gives me a 'Divide by Zero error encountered'. How can I check for zeros first.

I tried a case statement but cannot figure out where/how it goes. I put it in the demoninator, to check for zeros.

select u.displayname,

'percent Change' =   
--Entered Orders
(SELECT cast(COUNT(E.GUID) as decimal(3,0))
                          FROM CV3Order E
                          WHERE E.UserGUID = u.GUID
                          AND E.EnterRole = 'Physician'
			 AND E.Entered between @Period_1_FromDate and @Period_1_ToDate
			  )
/
--Requested Orders
(SELECT cast(COUNT(R.GUID) as decimal(3,0))
                          FROM CV3Order R
                          WHERE R.CareProviderGUID = u.GUID
			  AND u.occupationcode = 'MD'
			AND R.Entered between @Period_1_FromDate and @Period_1_ToDate
			  )

FROM CV3User u

 

[SQLDenis]

try this

select u.displayname,

'percent Change' =   
CASE (SELECT cast(COUNT(R.GUID) as decimal(3,0))
                          FROM CV3Order R
                          WHERE R.CareProviderGUID = u.GUID
              AND u.occupationcode = 'MD'
            AND R.Entered between @Period_1_FromDate and @Period_1_ToDate
              ) WHEN 0 THEN 0 ELSE
--Entered Orders
(SELECT cast(COUNT(E.GUID) as decimal(3,0))
                          FROM CV3Order E
                          WHERE E.UserGUID = u.GUID
                          AND E.EnterRole = 'Physician'
             AND E.Entered between @Period_1_FromDate and @Period_1_ToDate
              )
/
--Requested Orders
(SELECT cast(COUNT(R.GUID) as decimal(3,0))
                          FROM CV3Order R
                          WHERE R.CareProviderGUID = u.GUID
              AND u.occupationcode = 'MD'
            AND R.Entered between @Period_1_FromDate and @Period_1_ToDate
              ) END

FROM CV3User u

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/