create table #s ( 
table_id    int ,
member#     char( 11 ) ,
car_eff_dt  datetime ,
car_term_dt datetime ,
)

insert into #s values( 1 , '12345678901', '01/01/1995', '12/31/1995' )
insert into #s values( 2 , '12345678901', '01/01/1996', '12/31/1996' )
insert into #s values( 3 , '12345678901', '01/01/1997', '12/31/1997' )
insert into #s values( 4 , '10987654321', '07/15/1998', '02/16/1999' )
insert into #s values( 5 , '10987654321', '04/01/2000', '12/31/9999' )

-- Method #1: Uses a SELECT statement with a self-join & GROUP BY clause
select count( * ) as seq , s1.table_id , s1.member# , s1.car_eff_dt , s1.car_term_dt
from #s s1
join #s s2
on s1.member# = s2.member# and
   s2.car_eff_dt <= s1.car_eff_dt and
   s2.car_term_dt <= s1.car_term_dt
group by s1.table_id  , s1.member# , s1.car_eff_dt , s1.car_term_dt

-- Method #2 : A correlated query approach. This is typically slower
-- in SQL Server.
select (select count( * ) from #s s2
        where s1.member# = s2.member# and
              s2.car_eff_dt <= s1.car_eff_dt and
              s2.car_term_dt <= s1.car_term_dt ) as seq , 
        s1.*
from #s s1

This page was last updated on May 01, 2006 04:28 PM.