Binaryworld - Generate sequence numbers within a group of values. ... [ SQL Server -> T-SQL ]

This example shows how to generate sequence numbers within a group of values in a SELECT statement. With proper indexes & search conditions, the example using the SELECT statement with GROUP BY will be very efficient.

Click here to copy the following block

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