How to solve x + ceiling(x) +c = y, for x

user_14210810 0
Viewed 83

I am working on a SQL server project to make conversion between two types of car prices (eg, x is the original price, y is the dealer asking price) based on some rules, so that for every given dealer asking price y, I can get a corresponding original price x. The conversion rule I am having trouble with is this one: x + 5*ceiling(x/100) + some constant c = y, x=?

(It's more of a math problem actually. For example, if the rule is as easy as x + 10 = y, then x = y-10.)

math sql-server
2 Answers

Indeed, it's a math problem, that should be asked on Mathematics Stack Exchange.

That said, note that in the equation x+ceil(x)+c=y, x and y-c differ only by an integer (that depends on x). And since x+ceil(x) is an increasing function of x, if there is a solution it's unique.

Another remark:

  • for x in (0,1], x+ceil(x) lies in (1,2].
  • for x in (1,2], x+ceil(x) lies in (3,4].
  • ...

That is, there is a solution only if y-c is in (2n-1,2n] for some integer n. And then, x=y-c-n.

How do we find n? Well, y-c in (2n-1,2n] iff (y-c)/2 in (n-1/2,n]. So we must have n=ceil((y-c)/2).

It's not much more difficult for the equation x+5ceil(x/100)=y-c.

Now,

  • for x in (0,100], x+5ceil(x/100) in (5,105]
  • for x in (100,200], x+5ceil(x/100) in (110,210]
  • ...
  • for x in (100(n-1), 100n], x+5ceil(x/100) in (105n-100,105n]

Again, some values can't be reached, and if there is a solution, it's unique. And if y-c lies in (105n-100,105n] for some n, then x=y-c-5n.

Of course, you want to find n. Note that y-c in (105n-100,105n] iff (y-c)/105 is in (n-100/105,n], which is a subset of (n-1,n]. So if there is a solution, you must have n=ceil((y-c)/105).

user_139638670

Not a complete answer but for the simple case of Y = X + Ceiling(X)

declare @X float = 3.7
declare @Y float = 0.0
SELECT @y = @x + ceiling(@x)


SELECT CASE 
            WHEN (@y -floor(@y)) = 0 THEN @y/2
            ELSE            (@y - (@y -floor(@y)) - 1)/2 + (@y -floor(@y)) 
        END XValue

This works for all values of X that I've tested. The first case is for when X is a whole integer.

EDIT

For your case of Y = X + 5*CEILING(X)/100 we can surmise that if X is an integer then Y = X + 5/100 * X => 1.05 * X, if X is not an integer then y = X + 5/100(X+1) => 1.05*X + 0.05

So I think the following works for your case:

declare @X float = 3.1
declare @Y float = 0.0
SELECT @y = @x + 5*ceiling(@X)/100


SELECT CASE WHEN (ROUND(@y/1.05,0) = @y/1.05) THEN @y / 1.05
        ELSE ROUND((@y - 0.05) / 1.05,2) END

For your case with a constant, I can't see how to solve this without knowing the constant.

user_17503240
Related