Does this help you with getting you where you need to go?

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

It isn't a function, but it could easily be turned into one.

😎