To do this, I'm going to use another CTE. First, I'm going to get the cartesian product of gems x cuts, so 50,000 records, and calculate the profitability. Here's how I'm going to do that:

- The cut value is the uncut value * the multiplier
- The profit is the cut value - cost of the cut

/*;WITH

Profitability(cut, cost, multiplier, original_value, cut_value, profit)

AS (SELECT cut, cost, multiplier, u.uncut_value, (u.uncut_value * multiplier) , (u.uncut_value * multiplier) - cost

FROM @uncut_gem u

JOIN @gem

ON u.uncut_value * multiplier>= cost

),

So, for instance, here is the spread for a gem with an uncut value of 168:

Uncut Value | Cut | Cost | Multiplier | Cut Value | Profit |
---|---|---|---|---|---|

168 | Emerald | 10 | 1.25 | 210 | 200 |

168 | Oval | 20 | 1.50 | 252 | 232 |

168 | Pear | 35 | 1.75 | 294 | 259 |

168 | Plumbob | 50 | 2.00 | 336 | 286 |

168 | Marquis | 75 | 2.30 | 386 | 311 |

168 | Crystal Ball | 100 | 2.60 | 436 | 336 |

168 | Sculptor's Egg | 175 | 3.00 | 504 | 329 |

168 | Brilliant | 250 | 3.50 | 588 | 338 |

168 | Star Cut | 400 | 4.00 | 672 | 272 |

Continuing our query, we add another CTE. Now we group by the original value and find the maximum profit value for each original value. We'll be able to use this max profit to join back to the Profitability CTE and select just the Cuts with the highest profitability rating. If more than one cut has the same profitability for the same gem, this will return all cuts with that top profitability rating.

*/

BestCut(original_value, profit)

AS (SELECT original_value,MAX(profit)

FROM Profitability

WHERE original_value < 700

GROUP BY original_value

)

SELECT cut, cost, multiplier,

MIN(p.original_value) LowerBound,MAX(p.original_value) UpperBound,

CAST(MIN(p.original_value) * multiplier ASINT) Finished_Lower,

CAST(MAX(p.original_value) * multiplier ASINT) Finished_Upper,

CAST(MIN(p.profit) ASINT) Min_Profit,

CAST(MAX(p.profit) ASINT) Max_Profit

FROM Profitability p

JOIN BestCut bc

ON p.original_value = bc.original_value

AND p.profit = bc.profit

GROUP BY cut, cost, multiplier

ORDER BY cost ASC

Cut | Cost | Multiplier | Lower Bound | Upper Bound | Finished Lower | Finished Upper | Min Profit | Max Profit |
---|---|---|---|---|---|---|---|---|

Emerald | 10 | 1.25 | 8 | 41 | 10 | 51 | 0 | 41 |

Oval | 20 | 1.50 | 38 | 60 | 57 | 90 | 37 | 70 |

Pear | 35 | 1.75 | 59 | 60 | 103 | 105 | 68 | 70 |

Plumbob | 50 | 2.00 | 59 | 84 | 118 | 168 | 68 | 118 |

Marquis | 75 | 2.30 | 84 | 84 | 193 | 193 | 118 | 118 |

Crystal Ball | 100 | 2.60 | 84 | 167 | 218 | 434 | 118 | 334 |

Brilliant | 250 | 3.50 | 166 | 300 | 581 | 1050 | 331 | 800 |

Star Cut | 400 | 4.00 | 299 | 600 | 1196 | 2400 | 796 | 2000 |

Heart-Shaped | 1000 | 5.00 | 600 | 699 | 3000 | 3495 | 2000 | 2495 |

As you can see, there is some overlap, especially in the lower value cuts. I'm capping the original value at 700 for this because at 700, the Heart Cut returns a gem valued at 3500 Simoleans, which creates some interesting possibilities using another aspect of the game.

Next: Getting Transfiguration Figures