This might be helpful…
I’m always (nearly always) running into a problem finding, from a finite set of values that can be added together (or sometimes added or subtracted from one another) which values result in a specific amount. For instance, if I have the numbers [12, 15, 9, 5, 2] available to me, which combination would give me 16? Which would give me 22? This is often a problem encountered when reconciling two datasets with one another.
The script at the bottom of this message helps me solve this type of problem. As inputs, it takes the target value you’re looking for (with a +/- percentage), a flag to indicate whether or not the set of input numbers is subtractable (can be added and/or subtracted, or added only), and then the list of numbers inserted into an input table (#ColumnValues).
Which combination(s) would give me 16? Input the set of possible terms:
Then, set the parameters as follows:
A target percentage range of 0.0% instructs the query to return only precise matches (to the extent of SQL Server’s floating point arithmetic – we’re using FLOAT types for the COL_VAL column of the #ColumnValues table). The output looks like this:
The first table shows us (with target percentage set to 0.0%) that there are precisely 3 combinations of the number set that can be added and/or subtracted to give us precisely “16” (RESULT). The lower table shows for each of the three combinations (ROW_ID’s 65, 118 and 185) what values from the number set (COL_VAL) are added/subtracted to give us “16”. The COL_CALC value shows if that particular term is added “positive one” or subtracted “negative one”. Adding and/or subtracting the terms within each ROW_ID group gives us the RESULT value as shown in the upper table.
What combination of values gives us 22?
In this case we’re doing two things different than in the first example. First, we’re wanting to look only at combinations of terms that are added to equal 22 (ignore subtractable combinations). Also, rather than give a result set that’s precisely 22, look at those that are within +/- 5% of 22.
This result set shows us that there are 3 additive combinations that give us 22 (+/- 5%). If you look closely at the first table, you’ll see that ROW_ID = 27 gives us precisely the value we’re looking for (RESULT column equals 22). The lower table shows us the terms added to each other for each combination. Note that the COL_CALC values are only positive one (no negative one’s), which is a consequence of us setting the @Subtractable variable to 0 for “additive only”.
The complete script is as follows:
IF OBJECT_ID(‘tempdb.dbo.#Calc’) IS NOT NULL DROP TABLE #Calc
DECLARE
@TargetVal FLOAT = 22,
@TargetPct FLOAT = 0.05,
@Subtractable INT = 0
IF OBJECT_ID(‘tempdb.dbo.#ColumnValues’) IS NOT NULL DROP TABLE #ColumnValues
CREATE TABLE #ColumnValues (COL_ID INT IDENTITY(1, 1), COL_VAL FLOAT)
INSERT INTO #ColumnValues(COL_VAL) VALUES (12)
INSERT INTO #ColumnValues(COL_VAL) VALUES (15)
INSERT INTO #ColumnValues(COL_VAL) VALUES (9)
INSERT INTO #ColumnValues(COL_VAL) VALUES (5)
INSERT INTO #ColumnValues(COL_VAL) VALUES (2)
DECLARE
@N INT = (SELECT COUNT(*) FROM #ColumnValues),
@R BIGINT,
@C BIGINT = 1
SET @R = POWER((2 + @Subtractable), @N) * @N
IF OBJECT_ID(‘tempdb.dbo.#Calc’) IS NOT NULL DROP TABLE #Calc
CREATE TABLE #Calc (
ID BIGINT,
ROW_ID INT,
COL_ID INT,
COL_VAL FLOAT,
COL_CALC INT
)
WHILE @C <= @R
BEGIN
INSERT INTO #Calc(ID) SELECT @C
SET @C = @C + 1
END
UPDATE #Calc
SET
ROW_ID = ((ID – 1) / @N) + 1,
COL_ID = ((ID – 1) % @N) + 1,
COL_CALC = (((((ID – 1) / @N) + 1) – 1) / POWER((2 + @Subtractable), (((ID – 1) % @N) + 1) – 1)) % (2 + @Subtractable)
UPDATE #Calc SET COL_CALC = -1 WHERE COL_CALC = 2
DELETE FROM #Calc WHERE COL_CALC = 0
UPDATE A
SET
A.COL_VAL = (A.COL_CALC * B.COL_VAL)
FROM #Calc AS A
INNER JOIN #ColumnValues AS B
ON A.COL_ID = B.COL_ID
IF OBJECT_ID(‘tempdb.dbo.#ColumnValues’) IS NOT NULL DROP TABLE #ColumnValues
ALTER TABLE #Calc
DROP COLUMN ID, COL_ID
IF OBJECT_ID(‘tempdb.dbo.#Results’) IS NOT NULL DROP TABLE #Results
SELECT
ROW_ID,
SUM(COL_VAL) AS ‘RESULT’
INTO #Results
FROM #Calc
GROUP BY
ROW_ID
ORDER BY
SUM(COL_VAL)
SELECT
*,
@TargetVal AS ‘TARGET_VAL’,
@TargetPct AS ‘TARGET_PCT’
FROM #Results
WHERE RESULT BETWEEN (@TargetVal * (1 – @TargetPct)) AND (@TargetVal * (1 + @TargetPct))
ORDER BY
RESULT
SELECT
ROW_ID,
COL_CALC,
COL_VAL * COL_CALC AS ‘COL_VAL’,
@TargetVal AS ‘TARGET_VAL’,
@TargetPct AS ‘TARGET_PCT’
FROM #Calc
WHERE ROW_ID IN (
SELECT
ROW_ID
FROM #Results
WHERE RESULT BETWEEN (@TargetVal * (1 – @TargetPct)) AND (@TargetVal * (1 + @TargetPct))
)
ORDER BY
ROW_ID,
COL_VAL
Hope this helps.
Scott
Filed under: SQL Server, T-SQL | Tagged: Binary Table, Truth Table | Leave a Comment »
