SQLTeam.com | Weblogs | Forums

TSQL Challenge


I have a source db which stores student grades. I've created a simple query of studentId, Class Name and Grade columns for the purpose of exporting to an ascii csv to import into another grade tracking system (destination). The Grade column is numeric (0-100ish) in both systems not letter grades with source allowing 2 decimal places and destination not allowing any decimal so we are using standard rounding. So my current column reference in my query looks like this using the CAST to eliminate the trailing .00 which the round leaves behind:

CAST(ROUND(gbs.Average,0) AS INT) AS Grade

My trouble begins when I find out that the destination system has a validation rule of between 0 and 100 for the Grade column and the source allows grades to exceed the value of 100. So now I need the query's grade column to equal 100 if the source value is =>100 and I don't know how to do that inline without an if then statement which I want to avoid at all costs.
Any direction would be greatly appreciated.


CASE WHEN CAST(ROUND(gbs.Average,0) AS INT) > 100 THEN 100 ELSE CAST(ROUND(gbs.Average,0) AS INT) END AS Grade


at did it - thanks Scott