I have a function that adds line item numbers for courses grouped by
Identification number (NUID)

Right now it returns the following:

NUID  | CourseIndex
12345  1
12345  2
12346  1
12347  1
12348  1

But it actually needs to take into consideration one more item
RequiredDate:

so that the results look like this :
NUID |RequiredDate|CourseIndex
12345   2/10/07          1
12345   2/10/07          2
12345   3/31/07          3
12346   2/10/07          1     etc....

I would like to modify this function to take a RequiredDate field into
account...

=====================================

ALTER  function fn_AddCourseIndex (@tbl_or_vw varchar(120))
returns @outtable table
(
        [ID] [numeric](18, 0) NOT NULL ,
        [NUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [CourseIndex] [int] NULL

)
begin

declare @intable table (
        [ID] [numeric](18, 0) NOT NULL ,
        [NUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [CourseIndex] [int] NULL
)

declare @mycursor cursor
declare @id as numeric(18,0)
declare @nuid as varchar(50)
declare @lastnuid as varchar(50)
declare @cnt int

set @mycursor = cursor for
select [id], NUID
from tblTracking

open @mycursor
--print @@cursor_rows

set @cnt = 1
fetch next from @mycursor into @id, @nuid
while (@@fetch_status = 0)
begin
--do something here
  set  @lastnuid = @nuid
--  print @nuid + ' ' + cast(@cnt as varchar(5))
  insert into @intable (id,nuid,CourseIndex) values(@id,@nuid,@cnt)
  set @cnt = @cnt + 1
  fetch next from @mycursor into @id, @nuid
  if @nuid <> @lastnuid
    set @cnt = 1
end
close @mycursor
deallocate @mycursor

insert @outtable
select * from @intable
return
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Hi
USE Demo

GO

CREATE TABLE #t (row_ID INT NOT NULL PRIMARY KEY, NUID INT,RequiredDate
DATETIME)

GO

INSERT INTO #t VALUES (1,12345,'20071002')

INSERT INTO #t VALUES (2,12345,'20071002')

INSERT INTO #t VALUES (3,12345,'20070331')

INSERT INTO #t VALUES (4,12346,'20070331')

---SQL Server 2000/2005

SELECT * ,(SELECT COUNT(*) FROM #t t WHERE

t.row_ID<=#t.row_ID AND t.NUID=#t.NUID) AS CourseIndex

FROM #t

ORDER BY NUID

---SQL Server 2005

SELECT *,

ROW_NUMBER() OVER(

PARTITION BY NUID

ORDER BY RequiredDate ) AS CourseIndex

FROM #t

ORDER BY NUID

news:BD70B3B8-38B4-4B83-8323-BD32EC5FED83@microsoft.com...