create table tbl1
(ProcedID int, VisitID varchar(4), ChgAmt int, AmtAllowed int, CoPay int)

create table tbl2
(ProcedID int, BalDate datetime, BalAdjAmt int, BalCurAmt int)

Insert into tbl1 (ProcedID, VisitID, ChgAmt, AmtAllowed, CoPay) values (1, '145', 120, 70, 10) 
Insert into tbl1 (ProcedID, VisitID, ChgAmt, AmtAllowed, CoPay) values (2, '2500', 150, 75, 30)
Insert into tbl1 (ProcedID, VisitID, ChgAmt, AmtAllowed, CoPay) values (3, '145', 100, 50, 20)
Insert into tbl1 (ProcedID, VisitID, ChgAmt, AmtAllowed, CoPay) values (4, '2500', 250, 100, 20)
Insert into tbl1 (ProcedID, VisitID, ChgAmt, AmtAllowed, CoPay) values (5, '2500', 100, 50, 10)

Insert into tbl2 (ProcedID, BalDate, BalAdjAmt, BalCurAmt) values (1, '1/1/2002 13:23', 80, 60)
Insert into tbl2 (ProcedID, BalDate, BalAdjAmt, BalCurAmt) values (1, '7/1/2003', 40, 20)
Insert into tbl2 (ProcedID, BalDate, BalAdjAmt, BalCurAmt) values (2, '4/2/2003 6:00', 45, 105)
Insert into tbl2 (ProcedID, BalDate, BalAdjAmt, BalCurAmt) values (3, '5/1/2003', 20, 20) 
Insert into tbl2 (ProcedID, BalDate, BalAdjAmt, BalCurAmt) values (3, '6/1/2003', 10, 40) 
Insert into tbl2 (ProcedID, BalDate, BalAdjAmt, BalCurAmt) values (4, '5/2/2003', 40, 60) 
Insert into tbl2 (ProcedID, BalDate, BalAdjAmt, BalCurAmt) values (4, '7/1/2003', 40, 20)
Insert into tbl2 (ProcedID, BalDate, BalAdjAmt, BalCurAmt) values (5, '1/1/2001 13:30', 25, 75)
Insert into tbl2 (ProcedID, BalDate, BalAdjAmt, BalCurAmt) values (5, '2/2/2003 23:00', 15, 60)


Select 	VisitID, sum(BalCurAmt) BalCurAmt
From 	tbl1, tbl2 a
Where 	tbl1.ProcedID = a.ProcedID and
	BalDate = (Select max(BalDate)
		   From tbl2 b
		   Where tbl1.ProcedID = b.ProcedID)
Group By VisitID


VisitID BalCurAmt
-----------------
145	60
2500	185