question

You can use [`ISNULL()`][1] to test for null values and replace them with 0 Such as select a.audit_dt, isnull(a.inforce_count,0) as inforce_count, isnull(b.submitted_count,0) as submitted_count, insull(c.exception_count,0) as exception_count, isnull(d.cancelled_count,0) as cancelled_count from ..... --------- let me explain with an example why the isnull isn't giving you what you think it should declare @T1 table (id int, blah varchar(10)) declare @T2 table (id int, blah varchar(10)) insert into @T1 select 1,'T1 one' insert into @T1 select 2,'T1 two' insert into @T2 select 1,'T2 one' insert into @T2 select null,'T2 null' select T1.id , T1.blah , T2.id , T2.blah from (select [@T1].id, [@T1].blah from @T1) T1 full join (select isnull([@T2].id,0) as id, [@T2].blah from @T2) T2 on T1.id = T2.id The query above has the isnull() but the result set still shows null on the second row as this is a row produced by the outer join, where there is no corresponding record in T2 for the value of id = 1. The inner isnull() function is not invoked. id blah id blah ----------- ---------- ----------- ---------- 1 T1 one 1 T2 one 2 T1 two NULL NULL NULL NULL 0 T2 null By moving the isnull() to the outer layer of the query, we are applying the function to the full result set instead: select T1.id , T1.blah , isnull( T2.id ,0) as id, T2.blah from (select [@T1].id, [@T1].blah from @T1) T1 full join (select [@T2].id as id, [@T2].blah from @T2) T2 on T1.id = T2.id id blah id blah ----------- ---------- ----------- ---------- 1 T1 one 1 T2 one 2 T1 two 0 NULL NULL NULL 0 T2 null [1]: http://msdn.microsoft.com/en-us/library/ms184325.aspx