CleanCode::SQL::BoundSmoothing

This site will work and look better in a browser that supports web standards, but it is accessible to any browser or Internet device.

BoundSmoothing

This function implements a boundary smoothing algorithm as a table-valued function. It was designed for use with my Histogram stored procedure but is documented here separately to allow for other applications as well. It returns its inputs (upper and lower bounds); several intermediate, diagnostic values; and new upper and lower bounds that are more... palatable. In practice you only need the final two values; the others are present to allow for learning or tinkering, in case you wonder why certain values are smoothed to certain boundaries and wish to experiment.

Usage and Parameters

N U T S H E L L
Module	BoundSmoothing
Description	Returns upper and lower boundaries adjusted to round lots.
Environment	SQL Server 2000/2005
Author	Michael Sorens
Since	CleanCode 0.9.22

BoundSmoothing @LowerBoundInput, @UpperBoundInput

where:
   @LowerBoundInput ::= starting lower bound
   @UpperBoundInput ::= starting upper bound

@LowerBoundInput [ Type: int Default: -none- ]: Starting lower bound of the range to be smoothed.

@UpperBoundInput [ Type: int Default: -none- ]: Starting upper bound of the range to be smoothed.

Returns: table[Power int, Magnitude float, Closeness int, ClosenessToHigh float, ClosenessToZero float, LowerBoundInput int, UpperBoundInput int, LowerBoundOutput int, UpperBoundOutput int]: The single-row table returned contains the input values (LowerBoundInput, UpperBoundInput) smoothed to (LowerBoundOutput, UpperBoundInput) along with several intermediate, diagnostic values.

Details

The algorithm involves adjusting each boundary value so that it is divisible by an appropriate power of ten and giving the lower boundary a tendency towards zero when it is nearby. This is a rather qualitative statement; here are just a couple applications of this general principle:

This query invokes the function multiple times, putting all the results into a single result set for easy comparision:

SELECT * FROM dbo.BoundSmoothing(3, 21)
UNION SELECT * FROM dbo.BoundSmoothing(71, 76)
UNION SELECT * FROM dbo.BoundSmoothing(19, 76)
UNION SELECT * FROM dbo.BoundSmoothing(119, 176)
UNION SELECT * FROM dbo.BoundSmoothing(119, 276)
UNION SELECT * FROM dbo.BoundSmoothing(119, 976)
UNION SELECT * FROM dbo.BoundSmoothing(119, 676)
UNION SELECT * FROM dbo.BoundSmoothing(49, 976)
UNION SELECT * FROM dbo.BoundSmoothing(199, 899)
UNION SELECT * FROM dbo.BoundSmoothing(199, 901)
UNION SELECT * FROM dbo.BoundSmoothing(199, 976)
UNION SELECT * FROM dbo.BoundSmoothing(201, 976)
UNION SELECT * FROM dbo.BoundSmoothing(119, 1176)
UNION SELECT * FROM dbo.BoundSmoothing(119, 1499)
UNION SELECT * FROM dbo.BoundSmoothing(119, 1501)
UNION SELECT * FROM dbo.BoundSmoothing(319, 1576)
UNION SELECT * FROM dbo.BoundSmoothing(2319, 11576)
UNION SELECT * FROM dbo.BoundSmoothing(12383, 12389)
UNION SELECT * FROM dbo.BoundSmoothing(12319, 12389)
UNION SELECT * FROM dbo.BoundSmoothing(12319, 84111)
UNION SELECT * FROM dbo.BoundSmoothing(12319, 90111)
ORDER BY LowerBoundInput, UpperBoundInput

My article on the online technical journal DevX.com, A Generic Histogram Generator for SQL Server, describes the BoundSmoothing function, reviewing the results of the query shown here.