SELECT TAB1.SalesOrderId AS ID1, TAB1.TotalDue AS DUE1, TAB2.SalesOrderId AS ID2, TAB2.TotalDue AS DUE2FROM SALESTABLE TAB1CROSS JOINSALESTABLE TAB2WHERE TAB1.SalesOrderID <> TAB2.SalesOrderID ANDTAB1.TotalDue + TAB2.Totaldue = @SPECIFIED_DIFFERENCE
SELECT TAB1.SalesOrderId AS ID1, TAB1.TotalDue AS DUE1, TAB2.SalesOrderId AS ID2, TAB2.TotalDue AS DUE2FROM SALESTABLE TAB1JOIN SALESTABLE TAB2 ON TAB1.SalesOrderID < TAB2.SalesOrderID --Avoid half of the rows.WHERE TAB1.TotalDue < @SPECIFIED_DIFFERENCE --This are useless unless you have negatives.AND TAB2.TotalDue < @SPECIFIED_DIFFERENCE --Same as above.AND TAB1.TotalDue + TAB2.Totaldue = @SPECIFIED_DIFFERENCE
SELECT TAB1.SalesOrderId AS ID1, TAB1.TotalDue AS DUE1, TAB2.SalesOrderId AS ID2, TAB2.TotalDue AS DUE2FROM SALESTABLE TAB1JOINSALESTABLE TAB2ON TAB1.Totaldue = @SPECIFIED_DIFFERENCE - TAB2.Totaldue
DECLARE @Orders Table (OrderID VARCHAR(4), Amount MONEY)INSERT INTO @OrdersSELECT 2122, 5400UNION ALL SELECT '2123', 5500UNION ALL SELECT '2124', 1500UNION ALL SELECT '2125', 5700UNION ALL SELECT '2126', 4500UNION ALL SELECT '2126', 1500UNION ALL SELECT '2127', 5200UNION ALL SELECT '2129', 1000DECLARE @Amount MONEY = 7000;WITH UNIQUEnTuples (n, Tuples, Amount) AS ( SELECT 1 -- Add brackets around the OrderID to make later string comparison unique , CAST('[' + OrderID + ']' AS VARCHAR(max)) COLLATE Latin1_General_BIN ,Amount FROM @Orders UNION ALL SELECT 1 + n.n, a.OrderID + n.Tuples, n.Amount + t.Amount FROM @Orders t CROSS APPLY ( SELECT '[' + t.OrderID + ']') a(OrderID) JOIN UNIQUEnTuples n ON a.OrderID < n.Tuples WHERE CHARINDEX(a.OrderID, n.Tuples) = 0 AND n < 3 AND n.Amount + t.Amount <= @Amount)SELECT * FROM UNIQUEnTuplesWHERE Amount = @Amount -- The sum you're intereseted in identifying