-----------------------------------------------------------
--create table variables with sample data to test the statement below
set nocount on
declare @UserAccount Table
(UserAccountID int
,UserID int
,AccountID int)

declare @ActiveLog Table 
(Record int
,EnableDate datetime
,DisableDate datetime)

insert @UserAccount
Select 1, 1, 2 union all
Select 2, 1, 4 union all
Select 3, 1, 7

insert @ActiveLog
Select 1, '1/1/2005', '2/1/2005' union all
Select 2, '11/5/2004', null union all
Select 1, '3/1/2005', null union all
Select 3, '12/3/2004', '2/20/2005'
-----------------------------------------------------------

--Test Select statement against table variables and sample data

Select	ua.AccountID
	,agg.EnableDate
	,al.disableDate
From	@ActiveLog as al
JOIN	@UserAccount as ua 
		ON al.Record = ua.UserAccountID

JOIN	(--derived table with accountID and latest enabled date
	Select accountID
		,max(EnableDate) EnableDate
	From	@ActiveLog al2
	JOIN	@UserAccount ua2 ON al2.Record = ua2.UserAccountID
	Where	UserID = 1
	Group by AccountID
	) 
	as agg 
		--Join to derived table to only include latest
		--enableDate record for each account
		ON agg.accountID = ua.accountID 
		and agg.EnableDate = al.EnableDate
order by 1