Highest Level Completed But WHEN?

Transact-SQL
1

I currently have this

SELECT PersonId, FirstName, LastName, Mbr#, CONVERT(varchar, Birthdate, 101) AS Birthdate, Age, MAX(CONVERT(varchar, TestDate, 101)) AS LastTestDate, TestType, TestTrack, MAX(SkateTestLevelRank) AS HighestSkateTstLevel, Completed FROM dbo.v002TestsPassed WHERE (Passed = 1)

to display the highest skated level.

However when the TestType = "Dance" it needs to be marked as Completed.

How do you add, I'm thinking, an IF / CASE WHEN statement to display that correctly?

2

like this?

SELECT PersonId
      , FirstName
      , LastName
      , Mbr#
      , CONVERT(VARCHAR, Birthdate, 101) AS Birthdate
      , Age
      , MAX(CONVERT(VARCHAR, TestDate, 101)) AS LastTestDate
      , TestType
      , TestTrack
      , MAX(SkateTestLevelRank) AS HighestSkateTstLevel
      , CASE 
            WHEN TestType = 'Dance'
                  THEN 'Completed' AS Completed
            END
FROM dbo.v002TestsPassed
WHERE (Passed = 1)
3

Sorry, I realize I need to add more info.

The Complete field is a True or False field.

And for TestType = Dance, the TestCode starts with an "X" to identify that the Dance segment is complete.

So the Complete = 1 where TestCode starts with "X" BUT only for TestType=Dance, there are other TestTypes and do not matter on Complete or TestCode.

PER00020076, Ashley, Carlson ,1070633 ,11/24/1986, 29, 08/14/2015, Dance, Adult, 190 
PER00020076, Ashley, Carlson, 1070633, 11/24/1986, 29, 12/12/2010, Dance, Standard, 180
PER00020076, Ashley, Carlson, 1070633, 11/24/1986, 29, 11/10/2006, Free Skating, Standard, 50, 
PER00020076, Ashley, Carlson, 1070633, 11/24/1986, 29, 11/12/1998, Moves, Standard, 80
4

Assuming TestCode also is a column in your table,

....
, MAX(SkateTestLevelRank) AS HighestSkateTstLevel
, CASE 
        WHEN TestType = 'Dance' AND TestCode LIKE 'X%'
              THEN 1 ELSE 0 END AS Completed
        END
FROM dbo.v002TestsPassed   
....
5

Hi JamesK,
Thank you. I did something similar and it seems to be working.
I'll compare what I came up with to yours.

I'll post it when I can get on the SQL Server again.