"jonefer" <jonefer@discussions.microsoft.com> wrote in message
news:BD70B3B8-38B4-4B83-8323-BD32EC5FED83@microsoft.com...
>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
>

"Uri Dimant" wrote:

> 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
>
> "jonefer" <jonefer@discussions.microsoft.com> wrote in message
> news:BD70B3B8-38B4-4B83-8323-BD32EC5FED83@microsoft.com...
> >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
> >
>
>