none
TSQL Counting Number of Occurrences of a Text Value

    Question

  • 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.

    Tuesday, October 29, 2013 8:25 PM

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
    Tuesday, October 29, 2013 8:53 PM
    Moderator
  • 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
    Wednesday, October 30, 2013 4:16 PM
    Moderator

All replies

  • Which version of SQL Server are you using?  SQL Server 2012 has some functions what will handle this nicely.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, October 29, 2013 8:53 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,'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
    Tuesday, October 29, 2013 8:53 PM
    Moderator
  • 2008 R2 - and your example works nicely!  Thank you for your assistance.
    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.

    Wednesday, October 30, 2013 4: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
    Wednesday, October 30, 2013 4:16 PM
    Moderator
  • Thanks you - I didn't identify that spot for a change.
    Wednesday, October 30, 2013 4:57 PM