SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Double Counting and Sign Reversals


Double Counting and Sign Reversals

Author
Message
dwain.c
dwain.c
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21913 Visits: 6431
Comments posted to this topic are about the item Double Counting and Sign Reversals


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21913 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
bthomson
bthomson
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 69
you used a custom table value function called DelimitedSplit8k
probably works like most splits but... just thought you should know.
ericwood8
ericwood8
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 35
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.c
dwain.c
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21913 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21913 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Alan.B
Alan.B
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16477 Visits: 8085
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...

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
dwain.c
dwain.c
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21913 Visits: 6431
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. :-P

My first article Financial Rounding of Allocations is another example drawn from that experience.

Thanks for stopping by.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Chris_M
Chris_M
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 1948
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.c
dwain.c
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21913 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search