Gem Cuts: Finding Complex Ranges

I play a time-devouring, absolutely maddening, terrifically addictive game called The Sims.  And by "play" I mean "alternate between an obsession where I can play the game for ten hours a day and wake up thinking about it, and going cold turkey."  And not only do I play the game, I write SQL queries to analyze aspects of the game and form strategies for it, because I am a nerd.

The Sims is a game where the player gets to create a "household" of up to eight members, called Sims.  These Sims eat and sleep and bathe and go to work and paint masterpieces and write novels and learn new skills and vacation, and basically have lives that are much more interesting than yours because they don't spend them staring at a computer screen playing games (actually, some of them do that too...)

One of the aspects of the game is that the sims collect precious gems which, in the Sim world, are just lying around on the ground pretty much all over the place (in Sim world the Star of Africa is just another diamond...)  The Sims can then cut these gems.  Each cut has a different cost and applies a different value multiplier to the base value of the gem:

Cut	Cost	Multiplier
Emerald	10	1.25
Oval	20	1.50
Pear	35	1.75
Plumbob	50	2.00
Marquis	75	2.30
Crystal Ball	100	2.60
Sculptor's Egg	175	3.00
Brilliant	250	3.50
Star Cut	400	4.00
Heart-Shaped	1000	5.00

The question then is: when the Sim finds a gem, how do we know what the most profitable cut would be? To find out, I started by creating a CTE to generate the integers 1-5000. I capped it at 5000 for two reasons: 1) I haven't seen any gems in the game with an uncut value higher than 5000 and 2) if there was one, I already know it would be worth it to use the highest value cut.

DECLARE @uncut_gem TABLE (uncut_value int)DECLARE @gem TABLE (cut VARCHAR(25), cost INT, multiplier DECIMAL (3,2))
;WITH  a(n) AS (SELECT 1 n UNION ALL SELECT 1),  --  2 
    b(n) AS (SELECT a.n FROM a JOIN a a1 ON 1 = 1),  --  4 
    c(n) AS (SELECT b.n FROM b JOIN b b1 ON 1 = 1),  --  16 
    d(n) AS (SELECT c.n FROM c JOIN c c1 ON 1 = 1)  --  256
INSERT INTO @uncut_gem SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY d.n)
        FROM d JOIN c ON 1 = 1 JOIN a ON 1=1

INSERT INTO @gem
SELECT  'emerald', 10, 1.25
 UNION ALL SELECT  'oval', 20, 1.50
 UNION ALL SELECT  'Pear', 35, 1.75
 UNION ALL SELECT  'Plumbob', 50, 2.00
 UNION ALL SELECT  'Marquis', 75, 2.30
 UNION ALL SELECT  'Crystal Ball', 100, 2.60
 UNION ALL SELECT  'Sculptor''s Egg', 175, 3.00
 UNION ALL SELECT  'Brilliant', 250, 3.50
 UNION ALL SELECT  'Star Cut', 400, 4.00
 UNION ALL SELECT  'Heart-Shaped', 1000, 5.00

So now I have the base tables. Next, I'll run some numbers. First, I want to know where my break even points are. I want to know what value a gem has to be for the value of the cut gem to be at least the value of the cut plus the value of the original gemstone, so for instance, a plumbob cut costs 50 Simoleans (what other unit of currency would a Sim use?) and doubles the value of the gem. A rough gem with a 50 Simolean value would be worth 100 Simoleans with a plumbob cut, which would cover the cost of the cut plus the value of the original gem and leave a profit of 0. So I'm looking for (uncut * multiplier) - cost - uncut = 0, or to put it another way, uncut = cost/(multiplier - 1):

SELECT cut, cost, CAST(cost/(multiplier - 1) AS INT) Break_Even,
  CAST(cost * multiplier/(multiplier - 1)  AS INT) cut_value
FROM @gem

Here are the results:

Cut	Cost	Uncut Value	Cut Value
emerald	10	40	50
oval	20	40	60
Pear	35	46	81
Plumbob	50	50	100
Marquis	75	57	132
Crystal Ball	100	62	162
Sculptor's Egg	175	87	262
Brilliant	250	100	350
Star Cut	400	133	533
Heart-Shaped	1000	250	1250

Next: Maxing Profitability