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