NTILE Example: sp_examiner_selection

ALTER proc [dbo].[sp_examiner_selection]
(
@TermId varchar(50)
)
as
---- exec sp_examiner_selection 'CSE2012JUN_2K11Y1T1'
BEGIN
declare @Count int,@GlobalBariable int
declare @Examiner table
(
TermId varchar(50),
CourseId varchar(50),
CourseCode varchar(50),
Section varchar(50),
TeacherId varchar(50),
UserName varchar(50)
)
declare @Tabstring nvarchar(max)
declare @Tabname nvarchar(100)
set @Tabname='tblExaminer'
set @Tabstring='create table #' + @Tabname
select @GlobalBariable = COUNT(*) from (
select distinct CourseCode FROM T_Term_Teacher_Course TTC inner join T_Term_Course_Offer TCO on (TCO.TermId = TTC.TermId and TTC.CourseId = TCO.CourseId) where TTC.TermId = 'CSE2012JUN_2K11Y1T1' and TCO.CourseType = 'Theory'
) TTT
set @Count = @GlobalBariable
while (@Count > 0)
begin
insert into @Examiner
(
CourseCode,
CourseId,
TermId,
Section,
TeacherId,
UserName
)
select CourseCode,CourseId,TermId,'A' as Section,'','' from (
select CourseCode,CourseId,TermId,NTILE(@GlobalBariable) over (Order by CourseCode desc) rowIndex from (
SELECT distinct CourseCode,TTC.CourseId,TTC.TermId FROM T_Term_Teacher_Course TTC inner join T_Term_Course_Offer TCO on (TCO.TermId = TTC.TermId and TTC.CourseId = TCO.CourseId) where TTC.TermId = 'CSE2012JUN_2K11Y1T1' and TCO.CourseType = 'Theory'
) T ) TT where rowIndex = @Count
union
select CourseCode,CourseId,TermId,'B' as Section,'','' from (
select CourseCode,CourseId,TermId,NTILE(@GlobalBariable) over (Order by CourseCode desc) rowIndex from (
SELECT distinct CourseCode,TTC.CourseId,TTC.TermId FROM T_Term_Teacher_Course TTC inner join T_Term_Course_Offer TCO on (TCO.TermId = TTC.TermId and TTC.CourseId = TCO.CourseId) where TTC.TermId = 'CSE2012JUN_2K11Y1T1' and TCO.CourseType = 'Theory'
) T ) TT where rowIndex = @Count
set @Count = @Count - 1
end
SELECT * FROM @Examiner
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s