How do I define my own hardcoded Ceilings and Floors in Sql Server?

Question

Hi folks,

I have some sql statements that calculates some numbers. It's possible (with bonus points/penalty points) that a person could get a final score OVER 100 (with bonus points) or UNDER 0 (with penalties).

How can i make sure that the value calculated, if it exceeds 100 it get's maxed at 100. Alternatively, if the score ends up being below 0, then it's min'd at 0.

I'm assuming I could use a simple UDF that does this math - that's not hard. I'm not sure if this already exists?

I checked out CEILING and MAX and both of these are doing other things, not what I'm after.

Thoughts?

Answer 1

Do you mean that the score is stored in a column in a table, or is there some calculated total with these constraints?

In general, probably the easiest would be to put the logic into a trigger that would check to see what value is being inserted, and change it to your max or min if it's out of bounds.

Or, what I would do is use whatever abstraction level is inserting the value do the checking, since it's not a database integrity issue, but an application validation issue.

Answer 2

A trigger is indeed the correct (and only solution).

You need a trigger which fires BEFORE both UPDATE and INSERT (or two separate triggers, one for UPDATE and one for INSERT). Simply check the proposed new value for that column, and push it up or down if necessary.

http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx

The constraint becomes optional, but is very strongly recommended.

Answer 3

If you don't want to use a trigger then how about

MIN(MAX(XXX, 0), 100) where XXX is whatever existing code you have to calculate a score.

eg: MIN(MAX(enemiesKilled+10*LivesLeft+15sum(PrincessRescued), 0), 100)

Answer 4

Tes, it would be nice if SQL Server had the ANSI-SQL "horizontal" aggregate functions, then you could do exactly what the others have suggested: "MIN(Score, 100)", etc. Sadly, it doesn't so you cannot do it that way.

The typical way to do this is with CASE expressions:

SELECT
    CASE WHEN Score BETWEEN 0 AND 100 THEN Score
        WHEN Score < 0 THEN 0
        ELSE 100 END as BoundScore
FROM YourTable