Click here to Skip to main content
16,017,373 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
Is there an easy way to restrict values - returned by a SQL select - to certain boundaries?

I've got a some tables like this
<br />dataTBL:<br />dt                id    x<br />2008-01-01 10:00  1      5.13<br />2008-01-01 10:00  2     14.00<br />2008-01-01 10:00  3     -2.10<br />2008-01-01 10:00  4      1.65<br />2008-01-01 11:00  1      5.19<br />...<br /><br />compTBL (balancing computation rules):<br />resultID   baseID   k     d     minX   maxX<br />100        1        +1.0   0    null   null<br />100        2        -0.5   0    +3     null<br />100        3        -0.5  +5    -10     0<br />200        1        +1.0   0    null   null<br />200        4        -1.0   10   0      100<br />...<br />

And I want to calculate for ID 100 e.g. the values
<br />   5.13 * 1.0 + 0.0 (no limits)<br />+ 14.00 *-0.5 + 0.0 (but at least 3)<br />+ -2.10 *-0.5 + 5.0 (but at least -10 and maximal 0)<br />

I'm working with Sql Server 2005 and at the moment I'm doing it this way
<br />select <br />  dt,<br />  resultID,<br />  sum(<br />    case <br />      when isnull(x,0) * k + d < minX then minX<br />      when isnull(x,0) * k + d > maxX then maxX<br />      else isnull(x,0) * k + d<br />    end<br />  )    <br />from dataTBL join compTBL on dataID = baseID<br />group by dt, resultID<br />

This works fine, but I don't like having the same expression three times just for testing <, > and else.
Is there no shorter way to write this case-expression? Or ist there some other function I could use? Is there something like "r = MaximumOf(MinimumOf(x,maxX),minX)" - or even better "r = ClipToBoundaries(x,minX,maxX)"?

Thanks

Andy
Posted

1 solution

ScruffR wrote:
Is there something like "r = MaximumOf(MinimumOf(x,maxX),minX)" - or even better "r = ClipToBoundaries(x,minX,maxX)"?


How about creating an scalar-valued function ClipToBoundaries?

Regards,
Syed Mehroz Alam

 
Share this answer
 


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900