Cap field value in SELECT

Question

I am trying to cap particular fields so that they don't exceed a particular value.

For example, something like this would suffice, where I can specify that a field should not exceed 8:

SELECT (
        cap(t.MondayHrs,8) 
        + cap(t.TuesdayHrs,8)
        + cap(t.WednesdayHrs,8)
        + cap(t.ThursdayHrs,8)
        + cap(t.FridayHrs,8)
       ) as TotalHours

If MondayHrs = 7, then it should be added to TotalHours as 7. If MondayHrs = 10, then it should be added to TotalHours as 8 (my capped value)

Is there anything built into T-SQL that could facilitate this?

Heve you considered using CASE as in CASE WHEN t.MondayHrs < 8 THEN t.MondayHrs ELSE 8 END + CASE WHEN t.TuesdayHrs < 8 THEN t.TuesdayHrs ELSE 8 END + ...... — Steve Ford, Nov 14 '12 at 23:45
[this post](http://stackoverflow.com/questions/124417/is-there-a-max-function-in-sql-server-that-takes-two-values-like-math-max-in-ne) might help you.. Though you're more looking for a `min` function. — Mike Christensen, Nov 14 '12 at 23:45
User defined function looks good, I will look into it, thanks @MikeChristensen — joshschreuder, Nov 14 '12 at 23:47

score 6 · Accepted Answer · answered Nov 14 '12 at 23:49

6

create function Cap (@Value float,@maxValue float) Returns float 
as
begin
Declare @Result float
if @Value > @maxValue select @Result=@Maxvalue else select @Result=@Value
Return @Result
end;

usage

Select dbo.Cap(1,10),dbo.Cap(11,10)

answered Nov 14 '12 at 23:49

bummi

27,123
14
62
101

This is the path I went down, except I used the UDF from here: http://stackoverflow.com/a/124474/324817 – joshschreuder Nov 14 '12 at 23:59

score 4 · Answer 2 · answered Nov 25 '15 at 23:25

4

Try...

select least(t.MondayHrs,8) 
    + least(t.TuesdayHrs,8)
    + least(t.WednesdayHrs,8)
    + least(t.ThursdayHrs,8)
    + least(t.FridayHrs,8)
   ) as TotalHours

answered Nov 25 '15 at 23:25

Quickpick

163
6

most elegant solution! and not using GREATEST like @juergen-d suggested which actually does the opposite of what joshschreuder wants to accomplish. – Ludo - Off the record Sep 28 '18 at 10:08

score 2 · Answer 3 · answered Nov 14 '12 at 23:57

2

An alternate idea would be to use CASE. For example:

SELECT ( 
       (CASE WHEN t.MondayHrs > 8 THEN 8 ELSE t.MondayHrs END)
       + (CASE WHEN t.TuesdayHrs > 8 THEN 8 ELSE t.TuesdayHrs END)
) as TotalHours

answered Nov 14 '12 at 23:57

ItsPete

2,363
3
27
35

score 1 · Answer 4 · answered Nov 14 '12 at 23:48

1

You can write a function for that like MySQL GREATEST.

Then you could do something like

select greatest(some_col, 8) +
       greatest(other_col, 8) + 
       ...
from your_table

answered Nov 14 '12 at 23:48

juergen d

201,996
37
293
362

Cap field value in SELECT

4 Answers4