January 6, 2013 at 4:39 pm
Comments posted to this topic are about the item Double Counting and Sign Reversals
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
January 7, 2013 at 12:07 am
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 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
January 7, 2013 at 7:11 am
you used a custom table value function called DelimitedSplit8k
probably works like most splits but... just thought you should know.
January 7, 2013 at 12:38 pm
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.
January 7, 2013 at 5:23 pm
bthomson (1/7/2013)
you used a custom table value function called DelimitedSplit8kprobably 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 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
January 7, 2013 at 5:30 pm
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
January 8, 2013 at 1:30 pm
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...
-- Itzik Ben-Gan 2001
January 8, 2013 at 5:26 pm
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 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
January 11, 2013 at 12:23 pm
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
January 11, 2013 at 6:59 pm
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 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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply