declare @nbr table ( id int
,nbr varchar(12))
DECLARE @balance table( id int
,nbr varchar(12)
,date datetime
,balance money)
INSERT INTO @nbr values(1,26094)
INSERT INTO @nbr values(2,26328)
INSERT INTO @nbr values(3,30122)
INSERT INTO @nbr values(4,34099)
INSERT INTO @nbr values(5,81471)
INSERT INTO @nbr values(6,66443)
INSERT INTO @balance values(1, 26094, '2006-07-31', 6782.84)
INSERT INTO @balance values(1,26094, '2006-06-30', 5782.84)
INSERT INTO @balance values(1,26094, '2006-05-30', 2782.84)
INSERT INTO @balance values(2,26328, '2006-07-31', 1150.84)
INSERT INTO @balance values(2,26328, '2005-07-31', 6782.84)
INSERT INTO @balance values(5,81471, '2006-07-31', 9982.84)
INSERT INTO @balance values(5,81471, '2006-06-30', 42.84)
INSERT INTO @balance values(6,66443, '2006-07-31', 16782.84)
INSERT INTO @balance values(6,66443, '2006-07-31', 6782.84)
SELECT b.nbr
,b.date
,a.balance
FROM @balance a
JOIN
(
select b.id
,b.nbr
,max(b.date) as date
from @nbr a join @balance b on a.id = b.id
GROUP BY b.id,b.nbr
) b
ON a.id = b.id and a.date = b.date