InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:51:50 GMT20http://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]piyush.varma (7/23/2012)[/b][hr]Re: Super Fast DISTINCT using CTEs - by Paul WhiteHi Paul,How can I extend the technique to multiple columns please?Any clues please?Thank you,Piyush[/quote]It IS [i]VERY [/i]fast indeed! But only if the number of distinct rows out number those that have duplicates. If you have a lot of duplicate rows, you may want to revert back to a simple DISTINCT or GROUP BY clause.Mon, 23 Jul 2012 15:38:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxRe: Super Fast DISTINCT using CTEs - by Paul WhiteHi Paul,How can I extend your technique to multiple VARCHAR columns please?Any clues please?Thank you,PiyushMon, 23 Jul 2012 12:14:58 GMTpiyush.varmahttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxDear SQL Kiwi,Could you please explain little bit of your code, i mean what is happning behiend the scene (I am little week in T-SQL).Nice code by the way!! Thanks in advanceTue, 17 Jul 2012 23:14:24 GMTchauhanparixithttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxSorry... posted to the wrong thread... post removed.Sun, 04 Dec 2011 20:19:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxYWould have been much better, 50% space saved in the DB, and then remove the signature and you're off to the races! :w00t:Tue, 02 Nov 2010 02:04:45 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Chris Morris-439714 (11/1/2010)[/b][hr]Y.[/quote]That's a pretty efficient answer right there!Mon, 01 Nov 2010 20:08:43 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Ninja's_RGR'us (11/1/2010)[/b][hr][quote][b]Chris Morris-439714 (11/1/2010)[/b][hr][quote][b]Paul White NZ (11/1/2010)[/b][hr][quote][b]Chris Morris-439714 (11/1/2010)[/b][hr]Nah mate my walking-on-water skills are still underwhelming - don't even drink the stuff, fish f...never mind. Spill the beans! I'll try to understand, promise![/quote]SQLCLR, naturally - using Adam's Query Parallelizer :-)[/quote]Ooh you cheat, that's not a fix that's a different method altogether ;-)[/quote]How's that cheating?You never rewrote something from scratch to make it more efficient?[/quote]Y.Mon, 01 Nov 2010 08:46:24 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Chris Morris-439714 (11/1/2010)[/b][hr][quote][b]Paul White NZ (11/1/2010)[/b][hr][quote][b]Chris Morris-439714 (11/1/2010)[/b][hr]Nah mate my walking-on-water skills are still underwhelming - don't even drink the stuff, fish f...never mind. Spill the beans! I'll try to understand, promise![/quote]SQLCLR, naturally - using Adam's Query Parallelizer :-)[/quote]Ooh you cheat, that's not a fix that's a different method altogether ;-)[/quote]How's that cheating?You never rewrote something from scratch to make it more efficient?Mon, 01 Nov 2010 08:40:53 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Chris Morris-439714 (11/1/2010)[/b][hr]Ooh you cheat, that's not a fix that's a different method altogether ;-)[/quote]Yes I do cheat. Proud of it! :laugh:Mon, 01 Nov 2010 07:59:51 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Paul White NZ (11/1/2010)[/b][hr][quote][b]Chris Morris-439714 (11/1/2010)[/b][hr]Nah mate my walking-on-water skills are still underwhelming - don't even drink the stuff, fish f...never mind. Spill the beans! I'll try to understand, promise![/quote]SQLCLR, naturally - using Adam's Query Parallelizer :-)[/quote]Ooh you cheat, that's not a fix that's a different method altogether ;-)Mon, 01 Nov 2010 07:47:58 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Chris Morris-439714 (11/1/2010)[/b][hr]Nah mate my walking-on-water skills are still underwhelming - don't even drink the stuff, fish f...never mind. Spill the beans! I'll try to understand, promise![/quote]SQLCLR, naturally - using Adam's Query Parallelizer :-)Mon, 01 Nov 2010 07:44:52 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Paul White NZ (11/1/2010)[/b][hr][quote][b]Chris Morris-439714 (11/1/2010)[/b][hr]Heh - until you wrap it up nice and warm...[/quote]That helps - as usual - but it's still a bit slower than the (unmodified) XML PATH solution I posted earlier.My version also benefits from a suitable clustered index: CREATE UNIQUE CLUSTERED INDEX cuq ON #Test (Section, TestID). That makes it much faster, at least on my machine.Both solutions suck a bit because they can only use a single thread - can you guess how I'd fix that? ;-)[/quote]Nah mate my walking-on-water skills are still underwhelming - don't even drink the stuff, fish f...never mind. Spill the beans! I'll try to understand, promise!Mon, 01 Nov 2010 07:39:17 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Chris Morris-439714 (11/1/2010)[/b][hr]Heh - until you wrap it up nice and warm...[/quote]That helps - as usual - but it's still a bit slower than the (unmodified) XML PATH solution I posted earlier.My version also benefits from a suitable clustered index: CREATE UNIQUE CLUSTERED INDEX cuq ON #Test (Section, TestID). That makes it much faster, at least on my machine.Both solutions suck a bit because they can only use a single thread - can you guess how I'd fix that? ;-)Mon, 01 Nov 2010 07:32:14 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Jeff Moden (11/1/2010)[/b][hr]Damn! There is a trade off. If you don't know there's going to be dupes or not, pray for dupes when you use it. :-)[code="sql"]USE tempdb;GODROP TABLE dbo.Test;GOCREATE TABLE dbo.Test ( data INTEGER NOT NULL, );GOCREATE CLUSTERED INDEX c ON dbo.Test (data);GO-- Lots of duplicated valuesINSERT dbo.Test WITH (TABLOCK) (data)SELECT TOP (1000000) --CUT DOWN TO A MILLION FOR THIS TEST ROW_NUMBER() OVER (ORDER BY (SELECT 0)) --NO DUPESFROM master.sys.all_columns C1, master.sys.all_columns C2GO------------------------------------------------------------------DECLARE @BitBucket INT --TO TAKE THE DISPLAY PROCESSING OUT OF THE PICTURESET STATISTICS TIME ON;-- 1591ms CPUSELECT DISTINCT @BitBucket = dataFROM dbo.Test;-- 15ms CPUWITH RecursiveCTEAS ( SELECT data = MIN(T.data) FROM dbo.Test T UNION ALL SELECT R.data FROM ( -- A cunning way to use TOP in the recursive part of a CTE :) SELECT T.data, rn = ROW_NUMBER() OVER (ORDER BY T.data) FROM dbo.Test T JOIN RecursiveCTE R ON R.data < T.data ) R WHERE R.rn = 1 )SELECT @BitBucket = dataFROM RecursiveCTEOPTION (MAXRECURSION 0);SET STATISTICS TIME OFF;GODROP TABLE dbo.Test;[/code] Still, if you know you have a lot of dupes, this is the berries! :-)[/quote]No kidding. 100-fold improvement is astonishing.Mon, 01 Nov 2010 07:18:33 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Jeff Moden (11/1/2010)[/b][hr]Damn! There is a trade off. If you don't know there's going to be dupes or not, pray for dupes when you use it.... Still, if you know you have a lot of dupes, this is the berries! :-)[/quote]Yes, absolutely.Mon, 01 Nov 2010 07:18:00 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Paul White NZ (10/29/2010)[/b][hr[quote]...in the meantime, here's another simple and obvious use of rCTE's. No timings because I've not yet had the opportunity.[/quote]Table Spool mania! It doesn't perform well; I'd stick with the usual XML hack/solution:[/quote]Heh - until you wrap it up nice and warm:[code="sql"]-- make some simple sample data, 800,000 rowsDROP TABLE #SectionsSELECT TOP 100000 Section = ABS(CHECKSUM(NEWID())) INTO #Sections FROM dbo.syscolumns a, dbo.syscolumns bDROP TABLE #TestCREATE TABLE #Test (TestID INT IDENTITY (1,1), Section INT, Word VARCHAR(20))INSERT INTO #Test (Section, Word) SELECT s.Section, d1.Word FROM #Sections s, ( SELECT 'A' AS Word UNION ALL SELECT 'day' UNION ALL SELECT 'in' UNION ALL SELECT 'the' UNION ALL SELECT 'life' UNION ALL SELECT 'of' UNION ALL SELECT 'Ivan' UNION ALL SELECT 'Denisovich' ) d1ORDER BY s.Section-- prepare the data for use - part of the solutionDROP TABLE #PreparedDataSELECT TestID, Section, Word, WordStatus = CAST(CASE WHEN SectionID = 1 THEN 1 WHEN SectionID = SectionSize THEN 9 ELSE 0 END AS TINYINT)INTO #PreparedData FROM ( SELECT *, SectionID = ROW_NUMBER() OVER(PARTITION BY Section ORDER BY TestID), -- sets order of words in sentence SectionSize = COUNT(*) OVER(PARTITION BY Section) -- used to detect last word of sentence FROM #Test) dCREATE UNIQUE CLUSTERED INDEX CITestID ON #PreparedData ([TestID] ASC)-- 6 seconds-- consume the prepared data;WITH Concatenator AS ( SELECT TestID, Section, Word, -- source columns WordStatus, Sentence = CAST(Word AS VARCHAR(100)) -- "working" columns FROM #PreparedData WHERE TestID = 1 UNION ALL SELECT t.TestID, t.Section, t.Word, t.WordStatus, Sentence = CAST(CASE WHEN t.WordStatus = 1 THEN ISNULL(t.Word, '') ELSE l.Sentence + ' ' + ISNULL(t.Word, '') END AS VARCHAR(100)) FROM #PreparedData t INNER JOIN Concatenator l ON l.TestID + 1 = t.TestID)SELECT TestID, Section, SentenceFROM ConcatenatorWHERE WordStatus = 9OPTION (MAXRECURSION 0)-- 32 seconds: 100,000 (ish) rollup-up output rows[/code]Mon, 01 Nov 2010 07:13:12 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxDamn! There is a trade off. If you don't know there's going to be dupes or not, pray for dupes when you use it. :-)[code="sql"]USE tempdb;GODROP TABLE dbo.Test;GOCREATE TABLE dbo.Test ( data INTEGER NOT NULL, );GOCREATE CLUSTERED INDEX c ON dbo.Test (data);GO-- Lots of duplicated valuesINSERT dbo.Test WITH (TABLOCK) (data)SELECT TOP (1000000) --CUT DOWN TO A MILLION FOR THIS TEST ROW_NUMBER() OVER (ORDER BY (SELECT 0)) --NO DUPESFROM master.sys.all_columns C1, master.sys.all_columns C2GO------------------------------------------------------------------DECLARE @BitBucket INT --TO TAKE THE DISPLAY PROCESSING OUT OF THE PICTURESET STATISTICS TIME ON;-- 1591ms CPUSELECT DISTINCT @BitBucket = dataFROM dbo.Test;-- 15ms CPUWITH RecursiveCTEAS ( SELECT data = MIN(T.data) FROM dbo.Test T UNION ALL SELECT R.data FROM ( -- A cunning way to use TOP in the recursive part of a CTE :) SELECT T.data, rn = ROW_NUMBER() OVER (ORDER BY T.data) FROM dbo.Test T JOIN RecursiveCTE R ON R.data < T.data ) R WHERE R.rn = 1 )SELECT @BitBucket = dataFROM RecursiveCTEOPTION (MAXRECURSION 0);SET STATISTICS TIME OFF;GODROP TABLE dbo.Test;[/code] Still, if you know you have a lot of dupes, this is the berries! :-)Mon, 01 Nov 2010 06:54:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Jeff Moden (10/31/2010)[/b][hr]Absolutely awesome! What on earth made you look for an alternative to DISTINCT?[/quote]Thanks. It's a fair while ago now, so I don't remember exactly what the circumstances were, but for sure there was a column with very many duplicate values and it just seemed dumb to me that the optimizer would choose to scan the whole index rather than trying something a little more creative. I think it was around the time I was writing my article on paging, so it might have been at least tangentially related to that.Mon, 01 Nov 2010 00:06:19 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Paul White NZ (10/29/2010)[/b][hr]Super-fast DISTINCT using a recursive CTE:[/quote]Absolutely awesome! What on earth made you look for an alternative to DISTINCT?On my 8 year old 1.8GHz P4, here's what I get...[code="plain"](43 row(s) affected)SQL Server Execution Times: CPU time = 2938 ms, elapsed time = 3052 ms.(43 row(s) affected)SQL Server Execution Times: CPU time = 0 ms, elapsed time = 3 ms.[/code]Sun, 31 Oct 2010 23:53:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxSuper-fast DISTINCT using a recursive CTE:[code="sql"]USE tempdb;GODROP TABLE dbo.Test;GOCREATE TABLE dbo.Test ( data INTEGER NOT NULL, );GOCREATE CLUSTERED INDEX c ON dbo.Test (data);GO-- Lots of duplicated valuesINSERT dbo.Test WITH (TABLOCK) (data)SELECT TOP (5000000) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 117329FROM master.sys.columns C1, master.sys.columns C2, master.sys.columns C3;GO[/code][code="sql"]SET STATISTICS TIME ON;-- 1591ms CPUSELECT DISTINCT dataFROM dbo.Test;-- 15ms CPUWITH RecursiveCTEAS ( SELECT data = MIN(T.data) FROM dbo.Test T UNION ALL SELECT R.data FROM ( -- A cunning way to use TOP in the recursive part of a CTE :) SELECT T.data, rn = ROW_NUMBER() OVER (ORDER BY T.data) FROM dbo.Test T JOIN RecursiveCTE R ON R.data < T.data ) R WHERE R.rn = 1 )SELECT *FROM RecursiveCTEOPTION (MAXRECURSION 0);SET STATISTICS TIME OFF;GODROP TABLE dbo.Test;[/code]The recursive CTE is 100 times more efficient :-)Fri, 29 Oct 2010 21:54:02 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Chris Morris-439714 (10/29/2010)[/b][hr]I'd really like to see an example of that[/quote]I'll post one in a minute.[quote]...in the meantime, here's another simple and obvious use of rCTE's. No timings because I've not yet had the opportunity.[/quote]Table Spool mania! It doesn't perform well; I'd stick with the usual XML hack/solution:[code="sql"]SELECT Sections.Section, Concatenated.sentenceFROM ( SELECT DISTINCT Section FROM #Test ) SectionsCROSSAPPLY ( SELECT SPACE(1) + Word FROM #Test T WHERE T.Section = Sections.Section ORDER BY T.TestID FOR XML PATH (''), TYPE ) Concatenator (xml_string)CROSSAPPLY ( SELECT STUFF(Concatenator.xml_string.value('(./text())[1]', 'VARCHAR(MAX)'), 1, 1, SPACE(0)) ) Concatenated (sentence);[/code]Fri, 29 Oct 2010 21:15:11 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Paul White NZ (2/27/2010)[/b][hr]...recursive CTEs can be fast, but it is relative. I sometimes use them to seek down the distinct keys of a large index rather than scanning the whole thing and relying on an aggregate, for example... Paul[/quote]I'd really like to see an example of that - in the meantime, here's another simple and obvious use of rCTE's. No timings because I've not yet had the opportunity.[code="sql"]-- Rollup rows, concatenating row values into a new column DROP TABLE #TestCREATE TABLE #Test (TestID INT IDENTITY (1,1), Section INT, Word VARCHAR(20))INSERT INTO #Test (Section, Word) VALUES (1, 'A'),(1, 'day'), (1, 'in'),(1, 'the'),(1, 'life'),(1, 'of'),(1, 'Ivan'),(1, 'Denisovich'), (2, 'Silent'),(2, 'Spring') ;WITH PreparedData AS ( SELECT *, SectionID = ROW_NUMBER() OVER(PARTITION BY Section ORDER BY TestID), -- sets order of words in sentence SectionSize = COUNT(*) OVER(PARTITION BY Section) -- used to detect last word of sentence FROM #Test), Concatenator AS ( SELECT TestID, Section, Word, -- source columns SectionID, SectionSize, Sentence = CAST(Word AS VARCHAR(100)) -- "working" columns FROM PreparedData WHERE TestID = 1 UNION ALL SELECT t.TestID, t.Section, t.Word, t.SectionID, t.SectionSize, Sentence = CAST(CASE WHEN t.SectionID = 1 THEN ISNULL(t.Word, '') ELSE l.Sentence + ' ' + ISNULL(t.Word, '') END AS VARCHAR(100)) FROM PreparedData t INNER JOIN Concatenator l ON l.TestID + 1 = t.TestID)SELECT TestID, Section, SentenceFROM ConcatenatorWHERE SectionID = SectionSize[/code]Fri, 29 Oct 2010 10:04:30 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Jeff Moden (3/4/2010)[/b][hr]This is the link John tried to post...[url]http://www.imdb.com/title/tt0151804/[/url]It's a cult classic, totally silly, and (unfortunately) sometimes truer than life in a cube. :-P[/quote]and of course the main plot element is a scheme to skim fractions of pennies from interest rounding.Fri, 05 Mar 2010 10:32:07 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxThis is the link John tried to post...[url]http://www.imdb.com/title/tt0151804/[/url]It's a cult classic, totally silly, and (unfortunately) sometimes truer than life in a cube. :-PThu, 04 Mar 2010 19:25:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxJohn,That link seems to be broken, which is a shame since I would like to be one of the cool kids that's in on the joke. :unsure:PaulThu, 04 Mar 2010 17:35:37 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Jeff Moden (3/4/2010)[/b][hr]Ah... good that some have seen the movie.[/quote]To let the rest of the world in on it, we're talking about [url=http://www.imdb.com/title/tt0151804][b][u]Office Space[/u].[/b][/url] ("It's good to be a gangsta...." - ROFL).-----edit: fix link, typoThu, 04 Mar 2010 09:30:37 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxAh... good that some have seen the movie.Thu, 04 Mar 2010 00:16:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Paul White (3/2/2010)[/b][hr][quote][b]Jeff Moden (3/2/2010)[/b][hr]If you keep track of the pennies, you can probably afford to buy a new red stapler. ;-)[/quote]That would be awesome! I can never find a stapler when I need one.[/quote]Just don't get upset and burn the place down if you can't.Wed, 03 Mar 2010 11:26:35 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Jeff Moden (3/2/2010)[/b][hr]If you keep track of the pennies, you can probably afford to buy a new red stapler. ;-)[/quote]That would be awesome! I can never find a stapler when I need one.Tue, 02 Mar 2010 20:56:39 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxThe way I believe they do it in the states is with the interest rate out to "X" places as you say but the principle is rounded to the nearest penny each month or at the receipt of each payment if such a thing is allowed by the loan contract.Tue, 02 Mar 2010 18:26:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]Jeff Moden (3/2/2010)[/b][hr][quote][b]nathan 7372 (3/2/2010)[/b][hr]Thats the funny thing about decimal places. Technically, you are both right. Obviously Jeff's higher precision datatype leads to a more mathematically accurate answer. At the same time however, accounting is looking at having me calculate it with as little as 2 decimal places in order to save money. When all of those decimal places get truncated the company saves. So really precision has more to do with business requirements than it does with getting the "right" answer. Just food for thought.[/quote]I agree... you have to "close" the books each month on two decimal places. [/quote]I'm not sure about that being compliant with the law of [WhereverYouAre].AFAIK counting interest rates have to be at least precise to the third or even forth digit (guess what the scale of the MONEY data type is... ;-) ).Over here (Germany) there's some rumor about a guy who wrote a program for a bank and transferred the cumulated friction of Cents (aka rounding difference) per account and month to his own account.The bank took him to court for fraud. His statement was something like: "If I took money from somebody at all, it has been the owner of the accounts, but not the bank itself. The bank has no legal right of ownership of the rounding difference."Whether it's true or not, the banks over here now have to add up rounding differences. (I'm not sure if they do, though. ...;-) )Tue, 02 Mar 2010 14:12:27 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]nathan 7372 (3/2/2010)[/b][hr]Thats the funny thing about decimal places. Technically, you are both right. Obviously Jeff's higher precision datatype leads to a more mathematically accurate answer. At the same time however, accounting is looking at having me calculate it with as little as 2 decimal places in order to save money. When all of those decimal places get truncated the company saves. So really precision has more to do with business requirements than it does with getting the "right" answer. Just food for thought.[/quote]I agree... you have to "close" the books each month on two decimal places. As a side bar... If you keep track of the pennies, you can probably afford to buy a new red stapler. ;-)Tue, 02 Mar 2010 13:42:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]nathan 7372 (3/2/2010)[/b][hr]Thats the funny thing about decimal places. Technically, you are both right. Obviously Jeff's higher precision datatype leads to a more mathematically accurate answer. At the same time however, accounting is looking at having me calculate it with as little as 2 decimal places in order to save money. When all of those decimal places get truncated the company saves. So really precision has more to do with business requirements than it does with getting the "right" answer. Just food for thought.[/quote]After chewing on this "food for thought", I think the accountants may be right. Although you certainly want to use as high a precision as possible in calculations, the fact that the result is paid monthly means it should probably be expressed and saved rounded to two decimal places. The next month's calculation should use that actual monetary value as a base, not an imaginary 12 decimal place figure. It's not about shaving fractions for the company's benefit, but about accurately representing the real value of the account. You wouldn't cut a check for $354.395840049589, so why would you say that's the balance at the end of July?Tue, 02 Mar 2010 13:33:28 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxReminds me of a Superman film![img]http://www.filmsite.org/fotos/superman3_.jpg[/img]Tue, 02 Mar 2010 09:25:42 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxThats the funny thing about decimal places. Technically, you are both right. Obviously Jeff's higher precision datatype leads to a more mathematically accurate answer. At the same time however, accounting is looking at having me calculate it with as little as 2 decimal places in order to save money. When all of those decimal places get truncated the company saves. So really precision has more to do with business requirements than it does with getting the "right" answer. Just food for thought.Tue, 02 Mar 2010 09:22:59 GMTnathan 7372http://www.sqlservercentral.com/Forums/Topic873124-338-1.aspx[quote][b]nathan 7372 (3/1/2010)[/b][hr]Nope DECIMAL(19,12)[/quote]Thanks for the feedback!Decimal places and rounding considerations scare me. I freely admit to being a total amateur with that sort of thing.Last thing about that dollar-difference...do we know which was right/less wrong? It might be obvious that it is the method with a higher decimal scale, but I don't want to just [i]assume[/i] that...PaulMon, 01 Mar 2010 20:10:44 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxThe only question I would have asked is whether you'd confirmed with your business side that the interest is compounded monthly and not daily or continuously. To support daily compounding, you would add calculations for the number of days in each month and, of course, divide the annual rate by 365 (and maybe 366 in leap years if that's the way the rate was defined). "Continuous" compounding would involve using a formula based on powers of e, the root for natural logarithms.It seems clear that your requirements were for monthly compounding, regardless of the number of days elapsed, but I thought it worth while for others reading this thread to be made aware of possible wrinkles.Mon, 01 Mar 2010 19:15:05 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxNope DECIMAL(19,12)Mon, 01 Mar 2010 16:32:04 GMTnathan 7372http://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxThanks for the feedback. I am a bit confused, though... I thought Paul and I both used DECIMAL(28,17).Mon, 01 Mar 2010 16:09:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic873124-338-1.aspxI just wanted to let everyone know how things went. I have to say I learned a lot about the different ways to solve this problem. All three queries ran exceptionally fast. I had to modify Chris' query a tad since it was off by one month on the interest rates used. Since the interest rates are so similiar it only had a minor effect on the outcome.On an interesting note just by using the different datatypes between Jeff and Paul we get a difference of $374 out of a total around $4.5M. Just goes to show you the power of decimal places and rounding.Thank you all for tackling this problem. I really feel I learned a lot and the accounting department is ecstatic to finally have the numbers so we can move forward. Thank you again.Mon, 01 Mar 2010 13:42:59 GMTnathan 7372