SELECT
NAME=(CASE WHEN NOT EXISTS(SELECT 1 FROM Table_1 WHERE ID=A.Table_1 AND [Date]<A.[Date] THEN B.NAME END)),
A.[DATE],
A.[VALUE]
FROM
Table_1 A,
Table_2 B
WHERE
A.ID=B.ID
NAME=(CASE WHEN NOT EXISTS(SELECT 1 FROM Table_1 WHERE ID=A.Table_1 AND [Date]<A.[Date] THEN B.NAME END)),
A.[DATE],
A.[VALUE]
FROM
Table_1 A,
Table_2 B
WHERE
A.ID=B.ID
INSERT INTO #Table_1 SELECT 'A001','ABC'
INSERT INTO #Table_1 SELECT 'A003','XYZ'
INSERT INTO #Table_1 SELECT 'C005','IJK' CREATE TABLE #Table_2(ID VARCHAR(20),[Date] DATETIME,[Value] INT)
INSERT INTO #Table_2 SELECT 'A001','2005-10-16',90
INSERT INTO #Table_2 SELECT 'A001','2005-10-18',75
INSERT INTO #Table_2 SELECT 'A003','2005-10-13',180
INSERT INTO #Table_2 SELECT 'C005','2005-10-24',60
INSERT INTO #Table_2 SELECT 'C005','2005-10-26',70
INSERT INTO #Table_2 SELECT 'C005','2005-11-02',50 SELECT
[NAME]=(CASE WHEN NOT EXISTS(SELECT 1 FROM #Table_2 WHERE ID=A.ID AND [Date]<B.[Date]) THEN A.[NAME] END),
B.[DATE],
B.[VALUE]
FROM
#Table_1 A,
#Table_2 B
WHERE
A.ID=B.IDDROP TABLE #Table_1,#Table_2
from
Table_1
M left join
(select A.*,B.Date from Table_1 A inner join
(select ID,min(Data)as Date from Table_2 group by ID)B
on A.id=B.id)N
on M.id=N.Id and M.Date=N.Date
select T.Name,M.Date,M.Value from
(select C.Name,D.* from #Table_1 C inner join #Table_2 D on C.id=D.id)M left join (select ID,min(Date)as Date from #Table_2 group by ID)N
on M.id=N.Id and M.Date=N.Date
left join #Table_1 T
on N.id=T.ID
NULL 2005-10-18 00:00:00.000 75
XYZ 2005-10-13 00:00:00.000 180
IJK 2005-10-24 00:00:00.000 60
NULL 2005-10-26 00:00:00.000 70
NULL 2005-11-02 00:00:00.000 50