SQL to Identify Possible Addends for Given Sums: A+B+C+D = Sum

FindAddends2.sql

--List of Known Sums, each of which has 4 addends:  A + B + C + D = Sum
WITH desiredsums AS (
(SELECT 1 AS RowNum, 5929.49 AS DesiredSum) UNION 
(SELECT 2 AS RowNum, 14841.57 AS DesiredSum) UNION 
(SELECT 3 AS RowNum, 13947.95 AS DesiredSum) UNION 
(SELECT 4 AS RowNum, 13335.27 AS DesiredSum) UNION 
(SELECT 5 AS RowNum, 8681.99 AS DesiredSum) UNION 
(SELECT 6 AS RowNum, 9825.95 AS DesiredSum) UNION 
(SELECT 7 AS RowNum, 8860.13 AS DesiredSum) UNION 
(SELECT 8 AS RowNum, 12581.29 AS DesiredSum) UNION 
(SELECT 9 AS RowNum, 11991.42 AS DesiredSum) UNION 
(SELECT 10 AS RowNum, 9935.52 AS DesiredSum)  
),

--List of Addends That Create Sums
addends AS (
(SELECT 1 AS RowNum, 1359 AS Addend) UNION 
(SELECT 2 AS RowNum, 4588.78 AS Addend) UNION 
(SELECT 3 AS RowNum, 2277.59 AS Addend) UNION 
(SELECT 4 AS RowNum, 2942.93 AS Addend) UNION 
(SELECT 5 AS RowNum, 652.24 AS Addend) UNION 
(SELECT 6 AS RowNum, 2122.09 AS Addend) UNION 
(SELECT 7 AS RowNum, 794.05 AS Addend) UNION 
(SELECT 8 AS RowNum, 4671.67 AS Addend) UNION 
(SELECT 9 AS RowNum, 2022.73 AS Addend) UNION 
(SELECT 10 AS RowNum, 3284.97 AS Addend) UNION 
(SELECT 11 AS RowNum, 277.04 AS Addend) UNION 
(SELECT 12 AS RowNum, 3754.73 AS Addend) UNION 
(SELECT 13 AS RowNum, 3660.36 AS Addend) UNION 
(SELECT 14 AS RowNum, 949.01 AS Addend) UNION 
(SELECT 15 AS RowNum, 2315.85 AS Addend) UNION 
(SELECT 16 AS RowNum, 3298.29 AS Addend) UNION 
(SELECT 17 AS RowNum, 2191.23 AS Addend) UNION 
(SELECT 18 AS RowNum, 4426.18 AS Addend) UNION 
(SELECT 19 AS RowNum, 4471.75 AS Addend) UNION 
(SELECT 20 AS RowNum, 2584.66 AS Addend) UNION 
(SELECT 21 AS RowNum, 947.41 AS Addend) UNION 
(SELECT 22 AS RowNum, 3053.59 AS Addend) UNION 
(SELECT 23 AS RowNum, 3952.5 AS Addend) UNION 
(SELECT 24 AS RowNum, 4622.76 AS Addend) UNION 
(SELECT 25 AS RowNum, 941.12 AS Addend) UNION 
(SELECT 26 AS RowNum, 1577.85 AS Addend) UNION 
(SELECT 27 AS RowNum, 4886.75 AS Addend) UNION 
(SELECT 28 AS RowNum, 2360.91 AS Addend) UNION 
(SELECT 29 AS RowNum, 3735.72 AS Addend) UNION 
(SELECT 30 AS RowNum, 137.24 AS Addend) UNION 
(SELECT 31 AS RowNum, 3346.04 AS Addend) UNION 
(SELECT 32 AS RowNum, 3444.47 AS Addend) UNION 
(SELECT 33 AS RowNum, 4057.5 AS Addend) UNION 
(SELECT 34 AS RowNum, 4820.57 AS Addend) UNION 
(SELECT 35 AS RowNum, 4772.78 AS Addend) UNION 
(SELECT 36 AS RowNum, 2827.72 AS Addend) UNION 
(SELECT 37 AS RowNum, 988.1 AS Addend) UNION 
(SELECT 38 AS RowNum, 1122.53 AS Addend) UNION 
(SELECT 39 AS RowNum, 1761.22 AS Addend) UNION 
(SELECT 40 AS RowNum, 3928.65 AS Addend)  
),

--Cross JOIN Addends to Get All Permutations of Possible Sums
--Exclude addends from being added to themselves
allsums AS (
SELECT a1.RowNum AS RowNum1, a1.Addend AS Addend1, 
a2.RowNum AS RowNum2, a2.Addend AS Addend2, 
a3.RowNum AS RowNum3, a3.Addend AS Addend3, 
a4.RowNum AS RowNum4, a4.Addend AS Addend4, 
a1.Addend + a2.Addend + a3.Addend + a4.Addend AS TheSum FROM addends a1
CROSS JOIN addends a2
CROSS JOIN addends a3
CROSS JOIN addends a4
WHERE a1.RowNum <> a2.RowNum AND a1.RowNum <> a3.RowNum AND a1.RowNum <> a4.RowNum
AND a2.RowNum <> a3.RowNum AND a2.RowNum <> a4.RowNum
AND a3.RowNum <> a4.RowNum
),

--Find addend sums that match the Known Sums
possiblesums AS (
SELECT * FROM allsums a
JOIN desiredsums d ON d.DesiredSum = a.TheSum
),

distinctsums AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY possiblesums.DesiredSum ORDER BY possiblesums.DesiredSum) AS singlesum FROM possiblesums 
)

SELECT * FROM distinctsums WHERE distinctsums.singlesum = 1