关于两个表 字段相减问题 请教高手

[hksl]

费用表table1
id sname 房租 水费 电费 管理费 维修费 时间
1455 李四 2.500 60.000 30.0000 .0000 18.6666 2004-10-01
1001 1213 45.000 56.000 528.0000.0000 65.00 2004-10-02
1890 郑志坚 1.8000 54.0000 32.0000 .0000 6.0000 2004-10-03
1401 张三 1.8000 54.0000 32.0000 .0000 6.0000 2004-10-04

调整费用表 table2
id sname 房租 水费 电费 管理费 维修费 时间
1455 李四 1.5000 1.000 1.0000 .0000 2.00 2004-10-15
1001 1213 1.0000 5.000 5.0000 .0000 2.00 2004-10-04

我现在要得到这张表table3 (这张表可以用视图)
id sname 房租 水费 电费 管理费 维修费 时间
1455 李四 1.000 1.6000 29.0000 .0000 16.6666 2004-10-01
1001 1213 44.0000 51.000 523.0000.0000 63.00 2004-10-02
1890 郑志坚 1.8000 54.0000 32.0000 .0000 6.0000 2004-10-03
1401 张三 1.8000 54.0000 32.0000 .0000 6.0000 2004-10-04

table3 是 如果在table2中有编号相同和日期年月相同的， table1相应字段减去table2中字段值
如果没有就保持原有的，
我这样做了，但是不对
SELECT a.EmpId, a.SName, a.电费 - b.电费 AS eleExpense,
a.水费 - b.水费 AS waterExpense,
a.房租 - b.房租 AS RoomExpense,
a.管理费 - b.管理费 AS AdminExpense,
a.维修费 - b.维修费 AS ServiceExpense,
FROM dbo.VEmpExpense a left outer JOIN dbo.AdjustEmpExpense b
on a.EmpId = b.EmpId AND YEAR(a.SDatetime)
= YEAR(b.EDateTime) AND MONTH(a.SDatetime) = MONTH(b.EDateTime)
得到除了在table2中有的别的都没有结果
请问怎么做呢，谢谢

[Happiness]

你句的例子没算对

SELECT a.EmpId, a.SName, a.电费 - isnull(b.电费,0) AS eleExpense,
a.水费 - isnull(b.水费,0) AS waterExpense,
a.房租 - isnull(b.房租,0) AS RoomExpense,
a.管理费 - isnull(b.管理费,0) AS AdminExpense,
a.维修费 - isnull(b.维修费,0) AS ServiceExpense,
FROM dbo.VEmpExpense a left outer JOIN dbo.AdjustEmpExpense b
on a.EmpId = b.EmpId AND YEAR(a.SDatetime)
= YEAR(b.EDateTime) AND MONTH(a.SDatetime) = MONTH(b.EDateTime)
and a.SDatetime<=b.EDateTime --这个条件好像要吧

[hksl]

不要这个 只要年月相等就可以啦

[mgsray]

SELECT a.EmpId, a.SName, a.电费 - isnull(b.电费,0) AS eleExpense,
a.水费 - isnull(b.水费,0) AS waterExpense,
a.房租 - isnull(b.房租,0) AS RoomExpense,
a.管理费 - isnull(b.管理费,0) AS AdminExpense,
a.维修费 - isnull(b.维修费,0) AS ServiceExpense,
FROM dbo.VEmpExpense a left outer JOIN dbo.AdjustEmpExpense b
on a.EmpId = b.EmpId
AND convert(varchar(10),a.sdatetime,112)=convert(varchar(10),b.edatetime,112)

[hksl]

不是年月不对 的问题
而是得到的表 除了table2中有的 记录外 其他的 费用算出来的是null

[Happiness]

你看清楚

[Happiness]

实际上，有可能不对
如果一个人一个月改了两次，是不是叠加起来？
如果是，应该是：

SELECT a.EmpId, a.SName, a.电费 - isnull(b.电费,0) AS eleExpense,
a.水费 - isnull(b.水费,0) AS waterExpense,
a.房租 - isnull(b.房租,0) AS RoomExpense,
a.管理费 - isnull(b.管理费,0) AS AdminExpense,
a.维修费 - isnull(b.维修费,0) AS ServiceExpense,
FROM dbo.VEmpExpense a left outer JOIN (
select EmpId,YEAR(EDateTime) as YearXX,MONTH(EDateTime) as MonthXX,
sum(房租) as 房租,
sum(水费) as 水费,
sum(电费) as 电费,
sum(管理费) as 管理费
sum(维修费) as 维修费
min(EDateTime) as EDateTime
from dbo.AdjustEmpExpense
group by EmpId,YEAR(b.EDateTime),MONTH(b.EDateTime)
) as b
on a.EmpId = b.EmpId AND YEAR(a.SDatetime)
= b.YEARXX AND MONTH(a.SDatetime) = b.MONTHXX

[hksl]

谢谢