“TSQL Counting Number of Occurrences of a Text Value “


http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c6fbca5f-32dc-421a-8e16-08a9848dfd1e/tsql-counting-number-of-occurrences-of-a-text-value?forum=transactsql

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

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