calculation across tables 1

[anorthcote]

I'm having problems with a calcualtion across two tables. My query is

update salesperformance
set salesperformance.Yr1RYTD = (COALESCE salesperformance.yr1running,0) - COALESCE(salesperformance.yr2running,0) + buildsalesperformance.yeartotal)
from salesperformance
left outer join buildsalesperformance on salesperformance.br_no=buildsalesperformance.br_no
where buildsalesperformance.yr=right(@latestyear -1,2)

or in simpler terms

set column = tableA.value1 - tableA.value2 + table2.value1


I am using COALESCE to prevent a null value from stopping the calculation, the problem is that the select to get table2.value1 is returning no data as the data doesn't exist in table2.

What I want SQL to do is in the above case continue the calculation but substitute table2.value1 with 0 (zero) but i don't know how. As soon as the filter finds no results in table2 the calculation stops.

I hope this makes sense and that someone can help.

 

[bborissov]

update salesperformance
set salesperformance.Yr1RYTD =
                (COALESCE(salesperformance.yr1running,0) -
                 COALESCE(salesperformance.yr2running,0) +
                 buildsalesperformance.yeartotal)
from salesperformance
left join buildsalesperformance on
     salesperformance.br_no   = buildsalesperformance.br_no 
     AND buildsalesperformance.yr = right(@latestyear -1,2)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP