Skip to content

Instantly share code, notes, and snippets.

@steveendow
Last active Mar 14, 2021
Embed
What would you like to do?
SQL to Identify Possible Addends for Given Sums
--List of Known Sums
WITH desiredsums AS (
(SELECT 1 AS RowNum, 358.01 AS DesiredSum) UNION
(SELECT 2 AS RowNum, 802.45 AS DesiredSum) UNION
(SELECT 3 AS RowNum, 333.33 AS DesiredSum) UNION
(SELECT 4 AS RowNum, 777.77 AS DesiredSum)
),
--List of Addends That Create Sums
addends AS (
(SELECT 1 AS RowNum, 123.45 AS Addend) UNION
(SELECT 2 AS RowNum, 234.56 AS Addend) UNION
(SELECT 3 AS RowNum, 345.67 AS Addend) UNION
(SELECT 4 AS RowNum, 456.78 AS Addend) UNION
(SELECT 5 AS RowNum, 111.11 AS Addend) UNION
(SELECT 6 AS RowNum, 222.22 AS Addend) UNION
(SELECT 7 AS RowNum, 333.33 AS Addend) UNION
(SELECT 8 AS RowNum, 444.44 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,
a1.Addend + a2.Addend AS TheSum FROM addends a1
CROSS JOIN addends a2
WHERE a1.RowNum <> a2.RowNum
),
--Find addend sums that match the Known Sums
possiblesums AS (
SELECT * FROM allsums a
JOIN desiredsums d ON d.DesiredSum = a.TheSum
),
--Get distinct list of addend sums that match the Known Sums
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment