Count need to bring back a zero when no matches

I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches.

This is my code:

select d.lwia,

cast((d.closegoal + d1.opengoal) as float)denominator

from

(

select yg.lwia,

cast(count(yg.appid)as float) closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @.EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

)d,

(

-- Closure with open goal

select cast(count(yg.appid)as float) opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @.RWB

group by yg.lwia

)d1

)d2

I'd be tempted to put your results into a temptable or table variable and add the value 0 if the insert adds 0 rows.

eg

Code Snippet

DECLARE @.Table TABLE (Iwia INT, Denominator FLOAT)

INSERT INTO @.Table

SELECT......

IF @.@.ROWCOUNT = 0

INSERT INTO @.Table
VALUES (0, 0)

SELECT Iwia, Denominator
FROM @.Table

HTH!

|||

Use ISNULL(column, 0) around your two arguments.

e.g.

Code Snippet

select d.lwia,

cast((ISNULL(d.closegoal, 0) + ISNULL(d1.opengoal, 0)) as float)denominator

...

|||

Try this

SELECT d.lwia

--, cast((d.closegoal + d1.opengoal) as float)denominator

, SUM(goal) as denominator

FROM (

SELECT yg.lwia

,0 as goal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

--==========

UNION ALL

--==========

SELECT yg.lwia

,IsNull(cast(count(yg.appid)as int),0) as goal-- closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @.EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

--==========

UNION ALL

--==========

-- Closure with open goal

select yg.lwia

, IsNull(cast(count(yg.appid)as int) as goal--opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @.RWB

group by yg.lwia

)d

GROUP BY d.lwia