Sql-server – sql querying for a pattern

pattern matchingsql-server-2012t-sql

I was hoping someone could steer me in the right direction with this one. I have a table that has student grades.

Create Table StudentGrades
( 
  StudentID references Student(StudentID) int not null,
  GradeIssued varchar(1) not null,
  WhenIssued datetime not null default(getdate())
)

I'd like to write a query that does something like: Find students who have grades (in sequence) of a D, then a C, then another C, then an A. I know the direction (because of the date the grades are issued) that the pattern should be in, but I can't think of how to write this without using a cursor.

In my experience with querying I'm filtering through via WHERE and HAVING clauses, but I'm not sure where to go when I want to find a particular pattern.

Best Answer

One way of doing it would be to use LEAD.

WITH T AS
(
SELECT  StudentID,
        GradeIssued AS g1, 
        LEAD(GradeIssued, 1) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g2,
        LEAD(GradeIssued, 2) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g3,
        LEAD(GradeIssued, 3) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g4
FROM StudentGrades
)
SELECT DISTINCT StudentID
FROM   T
WHERE  g1 = 'D'
       AND g2 = 'C'
       AND g3 = 'C'
       AND g4 = 'A'

Another way would be to use XML PATH to concatenate all grades in order and LIKE.

WITH sg1 AS
(
SELECT DISTINCT StudentID
FROM StudentGrades
)
SELECT *
FROM sg1
CROSS APPLY
(
    SELECT GradeIssued + ''
    FROM StudentGrades sg2
    WHERE sg2.StudentID = sg1.StudentID
    ORDER BY WhenIssued
    FOR XML PATH('')
) CA(grades)
WHERE grades LIKE '%DCCA%'
Related Question