Solved

How to replace Null Values with a 0 in a stored procedure?

Posted on 2006-11-21
2
528 Views
Last Modified: 2012-05-05
I have the following stored procedure that works fine. I would like to tidy up one thing - the 'Jan' column displays data and null values, I would like to replace the null values with 0. Can anyone tell me how to do this (I am using SQL Server 2005)?

ALTER PROCEDURE [dbo].[RollingAFR]
(
@Year INT
) AS
BEGIN
SELECT O.Region, O.Description, (SELECT (Sum([VSeverity].[CountOfSeverity])*100000)/Sum([VHours].[Total]) AS AFR
FROM [VHours] LEFT JOIN VSeverity ON ([VHours].Unit_Office = VSeverity.Unit_Office) AND ([VHours].Year = VSeverity.Year) AND ([VHours].Period = VSeverity.Period)
WHERE ((([VHours].Unit_Office)= O.Description) AND (([VHours].Year)=@Year) AND (([VHours].Period)<=1)) OR ((([VHours].Unit_Office)= O.Description) AND (([VHours].Year)=(@Year-1)) AND (([VHours].Period)>1))
) AS Jan
FROM dbo.tbl_RegionOffice AS O
END
RETURN
0
Comment
Question by:kenmck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 76 total points
ID: 17986986
ALTER PROCEDURE [dbo].[RollingAFR]
(
@Year INT
) AS
BEGIN
SELECT O.Region, O.Description, coalesce((SELECT (Sum( coalesce([VSeverity].[CountOfSeverity],0) )*100000)/Sum([VHours].[Total]) AS AFR
FROM [VHours] LEFT JOIN VSeverity ON ([VHours].Unit_Office = VSeverity.Unit_Office) AND ([VHours].Year = VSeverity.Year) AND ([VHours].Period = VSeverity.Period)
WHERE ((([VHours].Unit_Office)= O.Description) AND (([VHours].Year)=@Year) AND (([VHours].Period)<=1)) OR ((([VHours].Unit_Office)= O.Description) AND (([VHours].Year)=(@Year-1)) AND (([VHours].Period)>1))
),0) AS Jan
FROM dbo.tbl_RegionOffice AS O
END
RETURN
0
 

Author Comment

by:kenmck
ID: 18018122
Thanks for that worked a treat.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question