SQL query for "capping threshold"

K

kbhat

Suppose I want to add all the numbers in one column, with the
following caveat:
All values should be capped at some high threshold level.

As an example, suppose the threshold is 10000 and the numbers in the
column are 7500, 8500, 9500, 10500, 11500 and 12500. The query should
return 555000 (which is 7500 + 8500 + 9500 + 10000 + 10000 + 10000).

Any help will be appreciated.

Thanks,
Bhat
 
J

Jerry Whittle

SELECT Sum(IIf([YourField]>10000,10000,[YourField])) AS TheValue
FROM tblCapped;

Insert the right field and table names. Also I think that your calculation
below has one too many zeros in it.
 
J

Jerry Whittle

SELECT Sum(IIf([YourField]>10000,10000,[YourField])) AS TheValue
FROM tblCapped;

Insert the right field and table names. Also I think that your calculation
below has one too many zeros in it.
 
D

Dale Fye

I have a function I use for a variety of purposes that will work great for
your purposes. You can pass this function as many parameters as you want
and it will return the minimum value. When I have multiple date fields in a
database and want to know the earliest date, I will use this.
Occassionally, usually when I run into an unnormallized database, when I
need to know the minimum number across several fields, I will use it also.
With a little modification, you can convert this to give you the maximum
value as well.

In your case, you would just use it as a cumputed column in your query.
Something like:

Select SUM(Minimum(10000, [yourField])) as ThresholdSum
From your table

HTH
Dale

******************
Public Function Minimum(ParamArray ArrayList()) As Variant

'Returns the minimum value from among the list of parameters passed
'Works on all data types
Dim lngLoop As Long
Dim LocalMin As Variant

For lngLoop = LBound(ArrayList) To UBound(ArrayList)
If IsNull(ArrayList(lngLoop)) Then
'skip to next array element
ElseIf IsEmpty(LocalMin) Then
LocalMin = ArrayList(lngLoop)
ElseIf ArrayList(lngLoop) < LocalMin Then
LocalMin = ArrayList(lngLoop)
End If
Next
Minimum = LocalMin

End Function
 
Top