create table #ActiveStatus (
    AcctId int,
    ActiveStatus bit not null, -- 1 = Active 0 = Inactive
    ActivityDate datetime
);
go
insert into #ActiveStatus (AcctId, ActiveStatus, ActivityDate)
select 1, 1, '2007-11-15' union all
select 1, 0, '2007-11-25' union all
select 1, 1, '2007-12-15' union all
select 2, 1, '2007-10-15' union all
select 2, 0, '2007-11-15' union all
select 3, 1, '2007-08-15' union all
select 3, 0, '2007-09-15' union all
select 3, 1, '2007-09-16' union all
select 3, 0, '2007-09-17' union all
select 3, 1, '2007-11-15' union all
select 4, 1, '2007-11-15';
go
select * from #ActiveStatus;
go
declare @StartDate datetime,
        @EndDate datetime,
        @AcctId int;
set @StartDate = '2007-10-01';
set @EndDate = '2007-12-31';
set @AcctId = 1;
with MaxActivity (
    AcctId,
    ActivityDate
) as (
select
    AcctId,
    max(ActivityDate)
from
    #ActiveStatus
where
    AcctId = @AcctId
    and ActivityDate >= @StartDate
    and ActivityDate < @EndDate
group by
    AcctId
)
select
    a.ActiveStatus
from
    #ActiveStatus a
    inner join MaxActivity ma
        on (a.AcctId = ma.AcctId
            and a.ActivityDate = ma.ActivityDate)
go
drop table #ActiveStatus;
go