Add Sequence Number within group

[scottcabral1979]

Hi,

I have the following data:

POL# POL_EFF_DATE
123 1-1-2012
123 1-1-2012
123 1-1-2012
123 1-1-2013
123 1-1-2013
456 1-1-2012
456 1-1-2012
456 1-1-2013
456 1-1-2013

I want to add sequence number to each group with same Pol# and Effective Date like this:

POL# POL_EFF_DATE Seq
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2013 2
123 1-1-2013 2
456 1-1-2012 1
456 1-1-2012 1
456 1-1-2013 2
456 1-1-2013 2

is there a set based function to accomplish this in SQL 2008?

thanks
Scott

 

[bborissov]

DECLARE @test TABLE (Pol int,  POL_EFF_DATE datetime)
INSERT INTO @Test VALUES (123,'1-1-2012')
INSERT INTO @Test VALUES (123,'1-1-2012')
INSERT INTO @Test VALUES (123,'1-1-2012')
INSERT INTO @Test VALUES (123,'1-1-2013')
INSERT INTO @Test VALUES (123,'1-1-2013')
INSERT INTO @Test VALUES (456,'1-1-2012')
INSERT INTO @Test VALUES (456,'1-1-2012')
INSERT INTO @Test VALUES (456,'1-1-2013')
INSERT INTO @Test VALUES (456,'1-1-2013')



SELECT *, 
       DENSE_RANK() OVER (PARTITION BY Pol ORDER BY Pol, POL_EFF_DATE) AS Seq
FROM @test

Borislav Borissov
VFP9 SP2, SQL Server

 

[scottcabral1979]

thanks Borislav ,

that worked!