I need to do an insert where one column is to be a calculated numerical value based on a value from another table, yet restricted between upper and lower bounds. Let's call these @lower_bounds and @upper_bounds (such that @lower_bounds < @upper_bounds).
So I might, as an example, want to do something like this:
INSERT o.[one_value] * (some calculation) AS [value]
INTO [table]
FROM [other_table] AS o
However, [value] must never be higher or lower than the bounds. So if the calculated [value] < @lower_bounds, then I need [value] = @lower_bounds instead.
And if [value] > @upper_bounds, then I need [value] = @upper_bounds.
The problem for me is having to do a unique calculation per row and not just pick the lowest between the result and a certain number, but also to pick the highest between the result and a certain other number at the same time.
Is there an easy way to do this in T-SQL? (There's a lot of data and cost has to be kept reasonably low.)
o.[big_value]boundedValue = lowerBound + randomValue * (upperBound - lowerBound) / maximumRandomValue(integer division assumed, otherwise you might need to round)?(small normally distributed random component calculated here), clamping that inside the final range will squeeze the entire distribution curve by the X axis instead of cutting it off on the edges.