Answered by:
TSQL Counting Number of Occurrences of a Text Value

Good afternoon,
I've been tasked with identifying number of consecutive F's in a students course history. My base query provides the sequential results below. However, I can't seem to get any code to generate the "Fgrades" column. This should represent an occurrence value of the F grade in the students chronological course history. You'll notice the counter is reset when a grade breaks up the consecutive requirement. I'd appreciate some input on how one might accomplish this task. I've spent quite a bit of time searching forums but can't identify information that applies to this nuance of Gaps and Islands. Thank you.
Question
Answers

create table test (SyStudentID int, AdEnrollID int, AdTermID int, AdGradeLetterCode char(1)) Insert into test values (1,5,1,'B') , (1,5,2,'B') , (1,5,3,'F') , (1,5,4,'B') , (1,5,5,'F') , (1,5,6,'F') , (1,5,7,'F') ;with mycte as (select *, row_number() Over(Order by AdTermID ) rn , row_number() Over(Partition by AdGradeLetterCode Order by AdTermID ) rn2 , row_number() Over(Order by AdTermID )  row_number() Over(Partition by AdGradeLetterCode Order by AdTermID ) rn3 from test ) Select SyStudentID ,AdEnrollID,AdTermID,AdGradeLetterCode , Case WHEN AdGradeLetterCode='F' Then row_number() Over(Partition by rn3 Order By AdTermID) Else 0 END FGrades from mycte Order By AdTermID drop table test
 Marked as answer by GFrank Tuesday, October 29, 2013 9:00 PM

create table #test (SyStudentID int, AdEnrollID int, AdTermID int, AdGradeLetterCode char(1)) Insert into #test values (1,5,1,'B') , (1,5,2,'B') , (1,5,3,'F') , (1,5,4,'B') , (1,5,5,'F') , (1,5,6,'F') , (1,5,7,'B') , (2,6,2,'B') , (2,6,3,'F') , (2,6,4,'F') , (2,6,5,'B') , (2,6,6,'F') , (2,6,7,'F') ;with mycte as (select *, row_number() Over(Order by SyStudentID, AdTermID ) rn , row_number() Over(Partition by SyStudentID, AdGradeLetterCode Order by AdTermID ) rn2 , row_number() Over(Order by SyStudentID, AdTermID )  row_number() Over(Partition by SyStudentID, AdGradeLetterCode Order by AdTermID ) rn3 from #test ) Select SyStudentID ,AdEnrollID,AdTermID,AdGradeLetterCode , Case WHEN AdGradeLetterCode='F' Then row_number() Over(Partition by SyStudentID,AdGradeLetterCode, rn3 Order By AdTermID) Else 0 END FGrades from mycte Order By SyStudentID, AdTermID drop table #test
 Marked as answer by GFrank Wednesday, October 30, 2013 4:58 PM
All replies


create table test (SyStudentID int, AdEnrollID int, AdTermID int, AdGradeLetterCode char(1)) Insert into test values (1,5,1,'B') , (1,5,2,'B') , (1,5,3,'F') , (1,5,4,'B') , (1,5,5,'F') , (1,5,6,'F') , (1,5,7,'F') ;with mycte as (select *, row_number() Over(Order by AdTermID ) rn , row_number() Over(Partition by AdGradeLetterCode Order by AdTermID ) rn2 , row_number() Over(Order by AdTermID )  row_number() Over(Partition by AdGradeLetterCode Order by AdTermID ) rn3 from test ) Select SyStudentID ,AdEnrollID,AdTermID,AdGradeLetterCode , Case WHEN AdGradeLetterCode='F' Then row_number() Over(Partition by rn3 Order By AdTermID) Else 0 END FGrades from mycte Order By AdTermID drop table test
 Marked as answer by GFrank Tuesday, October 29, 2013 9:00 PM


Jingyang,
I'm working on this example and have a secondary requirement. With one student this code is perfect. When I add a second student, however, it breaks and I'm not quite sure why. I'm in BOL and think it has to do with partitioning but haven't yet isolated the issue. In the graphic below, in row 12, I need to see the count restart like it does in row 5.
My modified code from the example:
create table #test (SyStudentID int, AdEnrollID int, AdTermID int, AdGradeLetterCode char(1)) Insert into #test values (1,5,1,'B') , (1,5,2,'B') , (1,5,3,'F') , (1,5,4,'B') , (1,5,5,'F') , (1,5,6,'F') , (1,5,7,'B') , (2,6,2,'B') , (2,6,3,'F') , (2,6,4,'F') , (2,6,5,'B') , (2,6,6,'F') , (2,6,7,'F') ;with mycte as (select *, row_number() Over(Order by SyStudentID, AdTermID ) rn , row_number() Over(Partition by SyStudentID, AdGradeLetterCode Order by AdTermID ) rn2 , row_number() Over(Order by SyStudentID, AdTermID )  row_number() Over(Partition by SyStudentID, AdGradeLetterCode Order by AdTermID ) rn3 from #test ) Select SyStudentID ,AdEnrollID,AdTermID,AdGradeLetterCode , Case WHEN AdGradeLetterCode='F' Then row_number() Over(Partition by rn3 Order By AdTermID) Else 0 END FGrades from mycte Order By SyStudentID, AdTermID drop table #test
Thank you for your time and assistance.

create table #test (SyStudentID int, AdEnrollID int, AdTermID int, AdGradeLetterCode char(1)) Insert into #test values (1,5,1,'B') , (1,5,2,'B') , (1,5,3,'F') , (1,5,4,'B') , (1,5,5,'F') , (1,5,6,'F') , (1,5,7,'B') , (2,6,2,'B') , (2,6,3,'F') , (2,6,4,'F') , (2,6,5,'B') , (2,6,6,'F') , (2,6,7,'F') ;with mycte as (select *, row_number() Over(Order by SyStudentID, AdTermID ) rn , row_number() Over(Partition by SyStudentID, AdGradeLetterCode Order by AdTermID ) rn2 , row_number() Over(Order by SyStudentID, AdTermID )  row_number() Over(Partition by SyStudentID, AdGradeLetterCode Order by AdTermID ) rn3 from #test ) Select SyStudentID ,AdEnrollID,AdTermID,AdGradeLetterCode , Case WHEN AdGradeLetterCode='F' Then row_number() Over(Partition by SyStudentID,AdGradeLetterCode, rn3 Order By AdTermID) Else 0 END FGrades from mycte Order By SyStudentID, AdTermID drop table #test
 Marked as answer by GFrank Wednesday, October 30, 2013 4:58 PM
