if object_id('[Tb1]') is not null drop table [Tb1]
go
create table [Tb1]([code] varchar(3),[number] int,[datetime] datetime)
insert [Tb1]
select '001',2,'2012-3-2' union all
select '001',5,'2012-3-3' union all
select '002',3,'2012-3-4' union all
select '001',5,'2012-3-4'
go
if object_id('[Tb2]') is not null drop table [Tb2]
go
create table [Tb2]([code] varchar(3),[datetime] datetime)
insert [Tb2]
select '001','2012-3-3' union all
select '002','2012-3-6' union all
select '001','2012-3-2' union all
select '003','2012-5-1'
go

select b.code,
  sum(case when a.[datetime]>=b.[datetime] then a.number else 0 end) as number,
  b.[datetime]
from (select code,max([datetime]) as [datetime] from tb2 group by code) b
left join tb1 a on a.code=b.code
group by b.code,b.[datetime]

/**
code number      datetime
---- ----------- -----------------------
001  10          2012-03-03 00:00:00.000
002  0           2012-03-06 00:00:00.000
003  0           2012-05-01 00:00:00.000

(3 行受影响)
**/

------解决方案--------------------
为什么发两贴

SQL code

--> 测试数据：[Tb1]
IF OBJECT_ID('[Tb1]') IS NOT NULL DROP TABLE [Tb1]
GO
CREATE TABLE [Tb1]([code] VARCHAR(3),[number] INT,[datetime] DATETIME)
INSERT [Tb1]
SELECT '001',2,'2012-3-2' UNION ALL
SELECT '001',5,'2012-3-3' UNION ALL
SELECT '002',3,'2012-3-4' UNION ALL
SELECT '001',5,'2012-3-4' UNION ALL
SELECT '004',6,'2012-1-1'
GO

IF OBJECT_ID('[Tb2]') IS NOT NULL DROP TABLE [Tb2]
GO
CREATE TABLE [Tb2]([code] VARCHAR(3),[datetime] DATETIME)
INSERT [Tb2]
SELECT '001','2012-3-3' UNION ALL
SELECT '002','2012-3-6' UNION ALL
SELECT '001','2012-3-2' UNION ALL
SELECT '003','2012-5-1'
GO

--> 测试语句：
SELECT isnull(a.[code],b.[code]) as [code],
sum(case when  a.[datetime]>=isnull(b.[datetime],0) then a.number else 0 end) as number,
max(b.[datetime]) as [datetime]
FROM [Tb1] a 
full join 
(select code,max([datetime]) as [datetime] from tb2 group by code) b
on a.[code]=b.[code]
group by a.[code],b.[code]
order by [code]
/*
code number      datetime
---- ----------- -----------------------
001  10          2012-03-03 00:00:00.000
002  0           2012-03-06 00:00:00.000
003  0           2012-05-01 00:00:00.000
004  6           NULL
警告: 聚合或其他 SET 操作消除了 Null 值。

(4 行受影响)

*/

------解决方案--------------------

SQL code

SELECT  a.code,sum(case when  a.[datetime]>=isnull(b.[datetime],0) then a.number else 0 end) as number,
 b.[datetime]  
FROM [Tb1] a 
left join 
(select code,max([datetime]) as [datetime] from tb2 group by code) b
on a.[code]=b.[code]
group by a.[code],b.datetime
order by [code]