select sum(case when priceuint='美元' then price*汇率 else price end)
from A表,B表
where A.contractno=b.contractno and adddate between '2012-1-1' and '2012-1-10'

------解决方案--------------------
select a.AddDate,SUM(b.Price)as total from a join b on a.ContractNO=b.ContractNO
where a.AddDate between '2012-1-1' and '2012-1-10'
group by a.AddDate

------解决方案--------------------
SQL code

declare @A表 table
(ID int,ContractNO varchar(7),AddDate datetime)
insert into @A表
select 1,'0120252','2011-12-12' union all
select 2,'0120373','2012-1-5' union all
select 3,'0120389','2012-1-15'
declare @B表 table 
(ID int,ContractNO varchar(7),ProductName varchar(5),Price numeric(4,2),PriceUnit varchar(4))
insert into @B表
select 1,'0120252','产品1',10.00,'元' union all
select 2,'0120252','产品2',20.00,'元' union all
select 3,'0120373','产品1',5.00,'美元' union all
select 4,'0120373','产品2',5.00,'美元' union all
select 5,'0120389','产品1',15.00,'元'

select sum(case when PriceUnit='美元' then 6.3161*price else price end) 
from @A表 a left join @B表 b on a.ContractNO=b.ContractNO
where a.AddDate between '2012-01-01' and '2012-01-10'

/*
63.161000
*/

------解决方案--------------------
SQL code

declare @a table(id int identity,contractno varchar(10),adddate datetime)
insert into @a(contractno,adddate) values('0120252','2011-12-12')
insert into @a(contractno,adddate) values('0120373','2012-1-5')
insert into @a(contractno,adddate) values('0120389','2012-1-15') 
declare @b table(id int identity,contractno varchar(10),productname varchar(10),price numeric(4,2),priceunit varchar(4))
insert into @b(contractno,productname,price,priceunit) 
select '0120252','产品1',10.00,'元' union all
select '0120252','产品2',20.00,'元' union all
select '0120373','产品1',5.00,'美元' union all
select '0120373','产品2',5.00,'美元' union all
select '0120389','产品1',15.00,'元'  
declare @from datetime,@to datetime
set @from='2011-1-5'
set @to='2012-1-15'
select SUM(price) total,priceunit from @b where contractno in (select contractno from @a where adddate between @from and @to)
group by priceunit

------解决方案--------------------
sum价格总计分类，left join 就可以了。

------解决方案--------------------
SQL code
create table COA
(
id int,
contractno varchar(20),
adddate date
)
create table COB
(
id int,
contractno varchar(20),
productname varchar(20),
price float,
priceUnit varchar(30)
)
insert into COA
select 1,'0120252','2011-12-12' union all
select 2,'0120373','2012-1-5' union all
select 3,'0120389','2012-1-15'
insert into COB
select 1,'0120252','产品1',10.00,'元' union all
select 2,'0120252','产品2',20.00,'元' union all
select 3,'0120373','产品1',5.00,'美元' union all
select 4,'0120373','产品2',5.00,'美元' union all
select 5,'0120389','产品1',15.00,'元'

select productname ,
SUM(case when priceUnit='美元' then price*6.134 else price end) totalPrice
from COA
left join COB on COA.contractno=COB.contractno
where adddate between '2011-01-01' and '2012-01-10'
group by productname;

------解决方案--------------------
不需要转换的：
SQL code