Post date: Jul 26, 2014 11:24:49 PM
"Find the four fractions, whre the numerator and denominators are 2 digits each and not divisible by 10, the denominators are larger than the numerators, and if you remove the digit that is the same between the two, you do not change the value. Find the product of these four, simply it to its most common terms, and give the denominator."
This is a wordy problem. Even simplified, it's a mouthful. The first part is simply a description of a WHERE clause. The second part, simplifying fractions, isn't fun in SQL, though.
CREATE TABLE #Digits (i char(1) PRIMARY KEY NOT NULL)
INSERT INTO #Digits VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
CREATE TABLE #Integers (i INT PRIMARY KEY NOT NULL)
;WITH Integers (i) AS
(
SELECT 10
UNION ALL
SELECT i + 1
FROM Integers
WHERE i < 99
)
INSERT INTO #Integers
SELECT i
FROM Integers
-- Capture all combinations of 2 digit numbers where the same digit can be found in the both sides of the fraction,
-- and where the result, with that digit taken out, is the same as the original ratio of the two numbers.
-- Then multiply the numerators and the denominators (the product = exp of sum of log).
; WITH Combos (l, r, ratio) AS
(
SELECT l.i, r.i, CONVERT(money,l.i)/CONVERT(money,r.i)
FROM #Integers l
CROSS JOIN #Integers r
WHERE l.i < r.i
)
SELECT CONVERT(int,EXP(SUM(LOG(l)))) AS l, CONVERT(int,EXP(SUM(LOG(r)))) AS r
INTO #Answer
FROM Combos c
INNER JOIN #Digits d
ON c.l LIKE '%' + d.i + '%'
AND c.r LIKE '%' + d.i + '%'
AND CONVERT(money,NULLIF(REPLACE(c.l, d.i, ''),'')) / CONVERT(money,NULLIF(NULLIF(REPLACE(c.r, d.i, ''),'0'),'')) = ratio
-- If I look at the content of #Answer, it's obvious what the denominator is. But this is about programmed solutions. Only AI, not HI, would be a proper solution, and that's beyond my SQL skill.
-- So here's a brute force reduction. Just keep dividing by numbers for as long as you can divide evenly until you run out of numbers to divide by.
DECLARE @c int
SELECT @c = SQRT(l)
FROM #Answer
WHILE 1 = 1
BEGIN
WHILE 1 = 1
BEGIN
UPDATE #Answer
SET l = l/@c, r = r/@c
WHERE l % @c = 0
AND r % @c = 0
IF @@ROWCOUNT = 0 BREAK
END -- inner while
SET @c = @c - 1
IF @c = 1 BREAK
END -- outer while
SELECT *
FROM #Answer
DROP TABLE #Integers
DROP TABLE #Digits
DROP TABLE #Answer