ericwood8 (1/7/2013)
First off, I love this article.On Sign Reversals section, imagine that entry 7 & 8 were $500 instead of $400. :
UNION ALL SELECT 500, '+' -- [Assigned ID is 10007]
UNION ALL SELECT -500, '-' -- [Assigned ID is 10008]
The routine fails to note that the combinations where could be off by the ($800 - $500) entries.
Try this:
CREATE TABLE #Trans
( ID INT IDENTITY(10001,1)
, Amount MONEY
, [Sign] CHAR(1)
)
TRUNCATE TABLE #Trans
INSERT INTO #Trans (Amount, [Sign])
SELECT 100, '+' -- [Assigned ID is 10001]
UNION ALL SELECT -100, '+' -- [Assigned ID is 10002]
UNION ALL SELECT 200, '-' -- [Assigned ID is 10003]
UNION ALL SELECT -200, '-' -- [Assigned ID is 10004]
UNION ALL SELECT 300, '-' -- [Assigned ID is 10005]
UNION ALL SELECT -300, '+' -- [Assigned ID is 10006]
--UNION ALL SELECT 400, '+' -- [Assigned ID is 10007]
--UNION ALL SELECT -400, '-' -- [Assigned ID is 10008]
UNION ALL SELECT 500, '+' -- [Assigned ID is 10007]
UNION ALL SELECT -500, '-' -- [Assigned ID is 10008
UNION ALL SELECT 800, '+' -- [Assigned ID is 10009]
UNION ALL SELECT -800, '-' -- [Assigned ID is 10010]
SELECT * FROM #Trans
DECLARE @ReferenceTotal MONEY = 1592.24
,@Delimiter CHAR(1) = ','
,@ActualTotal MONEY = (SELECT SUM(Amount)FROM #Trans)
DECLARE @AmountOfInterest MONEY = @ActualTotal - @ReferenceTotal
SELECT @ReferenceTotal = 2400
,@ActualTotal = SUM(CASE [Sign] WHEN '+' THEN Amount
ELSE -Amount END)
FROM #Trans
-- Problem #3: Identify transactions with reversed signs
-- Solution #3: Create the Tuples based on subtracting 2* the amount from
-- the actual total.
;WITH UNIQUEnTuples (n, Tuples, ID, Total)
AS
(
SELECT 1, CAST(ID AS VARCHAR(8000)), ID
,@ActualTotal - 2 * CASE [Sign] WHEN '+' THEN Amount ELSE -Amount END
FROM #Trans
UNION ALL
SELECT 1 + n.n, CAST(t.ID AS VARCHAR(8000)) + ',' + n.Tuples, t.ID
,n.Total - 2 * CASE [Sign] WHEN '+' THEN t.Amount ELSE -t.Amount END
FROM UNIQUEnTuples n
CROSS APPLY (
SELECT ID, Amount, [Sign]
FROM #Trans t
WHERE t.ID < n.ID) t
-- Limit the recursion depth (to 3-Tuples)
WHERE n < 5
)
SELECT n, [Reversed Sign Tuples]=Tuples, Total
FROM UNIQUEnTuples
WHERE Total = @ReferenceTotal
DROP TABLE #Trans
Note the line of code near the bottom in bold. This is the "governor" that controls the depth of recursion. It turns out, that those Tuples only appear when you change the governor as shown.
Thanks anyway for dropping by and giving it a try.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St