Double Counting and Sign Reversals

  • Dwain Camps

    SSC Guru

    Points: 86893

    Comments posted to this topic are about the item Double Counting and Sign Reversals


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • Dwain Camps

    SSC Guru

    Points: 86893

    Not sure what happened, but the first script under the Sign Reversals section should look like this:

    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 800, '+' -- [Assigned ID is 10009]

    UNION ALL SELECT -800, '-' -- [Assigned ID is 10010]

    SELECT * FROM #Trans

    SELECT SUM(CASE [Sign] WHEN '+' THEN Amount ELSE -Amount END) FROM #Trans


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • bthomson

    Valued Member

    Points: 62

    you used a custom table value function called DelimitedSplit8k

    probably works like most splits but... just thought you should know.

  • ericwood8

    SSC Enthusiast

    Points: 174

    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.

  • Dwain Camps

    SSC Guru

    Points: 86893

    bthomson (1/7/2013)


    you used a custom table value function called DelimitedSplit8k

    probably works like most splits but... just thought you should know.

    Hi bthomson! Thanks for dropping by.

    There is a link in the article to the DelimitedSplit8K article but here it is again:

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    It is pretty well known in the SQLverse, but since you seem to be relatively new to the forum, perhaps you haven't seen it before.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • Dwain Camps

    SSC Guru

    Points: 86893

    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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • Alan Burstein

    SSC Guru

    Points: 61087

    Great article Dwain! It also inspired me to read the N-Tuple article which I liked.

    It’s almost a shame (not really! J) that I no longer support Accounting applications because these scripts would undoubtedly save me countless hours of manual reconciliation.

    I supported accounting applications and could have used these (my first SQL job was supporting an accounting app that ran on NT4, SQL 6.5). I could have also used this then or during the parts of my college classes where I where we had to figure out why the books did not balance.

    Edit: Didn't run out of stuff to say... accidently hit enter and posted incomplete comment...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Dwain Camps

    SSC Guru

    Points: 86893

    Alan.B (1/8/2013)


    Great article Dwain! It also inspired me to read the N-Tuple article which I liked.

    It’s almost a shame (not really! J) that I no longer support Accounting applications because these scripts would undoubtedly save me countless hours of manual reconciliation.

    I supported accounting applications and could have used these (my first SQL job was supporting an accounting app that ran on NT4, SQL 6.5). I could have also used this then or during the parts of my college classes where I where we had to figure out why the books did not balance.

    Edit: Didn't run out of stuff to say... accidently hit enter and posted incomplete comment...

    Glad you liked it Alan. I think it may be difficult for some people that haven't supported bean counters to understand how inane and time consuming some of their problems are. 😛

    My first article Financial Rounding of Allocations[/url] is another example drawn from that experience.

    Thanks for stopping by.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • Chris_M

    Old Hand

    Points: 326

    Consider this stuff IMPLEMENTED before next closing.

    Every year we have two folk locked in a room for days, reading thousands of numbers at each other trying to track down why some stupid totals don't match.

    EXCELLENT!

    Thanks so much. Chris

  • Dwain Camps

    SSC Guru

    Points: 86893

    Chris_M (1/11/2013)


    Consider this stuff IMPLEMENTED before next closing.

    Every year we have two folk locked in a room for days, reading thousands of numbers at each other trying to track down why some stupid totals don't match.

    EXCELLENT!

    Thanks so much. Chris

    It is always gratifying to me when people read my articles, but hearing that the information provided will actually be used is even more so.

    Thanks Chris and best of luck with it!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply