0

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.)

n00bd00d
  • 5
  • 2
  • what type is `o.[big_value]` – Mark Schultheiss Apr 27 '23 at 12:11
  • What about limiting the random value to the exact bounds you need, e.g. as `boundedValue = lowerBound + randomValue * (upperBound - lowerBound) / maximumRandomValue` (integer division assumed, otherwise you might need to round)? – Aconcagua Apr 27 '23 at 12:47
  • 1
    @Aconcagua That is what I assume Thom A meant when closing as duplicate of https://stackoverflow.com/questions/1045138/how-do-i-generate-a-random-number-for-each-row-in-a-t-sql-select. It should however give a different value distribution. The OP specifically mentions that there is a `(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. – GSerg Apr 27 '23 at 12:53

0 Answers0