Changing a SUM returned NULL to zero
- by Lee_McIntosh
I have a Stored Procedure as follows:
CREATE PROC [dbo].[Incidents]
(@SiteName varchar(200))
AS
SELECT
(
SELECT SUM(i.Logged)
FROM tbl_Sites s
INNER JOIN tbl_Incidents i
ON s.Location = i.Location
WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)
GROUP BY s.Sites
) AS LoggedIncidents
'tbl_Sites contains a list of reported on sites.
'tbl_Incidents containts a generated list of total incidents by site/date (monthly)
'If a site doesnt have any incidents that month it wont be listed.
The problem I'm having is that a site doesnt have any Incidents this month and as such i get a NULL value returned for that site when i run this sproc, but i need to have a zero/0 returned to be used within a chart in SSRS.
I've tried the using coalesce and isnull to no avail.
SELECT COALESCE(SUM(c.Logged,0))
SELECT SUM(ISNULL(c.Logged,0))
Is there a way to get this formatted correctly?
Cheers,
Lee
