March 1, 2015 at 11:51 pm
Solomon Rutzky (3/1/2015)
dwain.c (2/24/2015)
There's a loan amortization example here: Exploring Recursive CTEs by Example[/url]It won't be as fast as a QU approach, but the example does handle multiple loans and if I recall correctly it also handles the final penny rounding.
So I finally had a chance to test your rCTE method and had a few notes. Again, just like with the items I mentioned related to Greg's method, I am not being negative and this doesn't take anything away from what is clearly an excellent article.
- The final payment should calculated, not assumed to be the original payment calculation. It needs to be (Interest applied to beginning balance of the final month) + (beginning balance of the final month). I believe this is fixable in your current model since you seem to be handling final payment details via
CASE PaymentNo + 1 WHEN Period THEN- The monthly payment amount and monthly interest amounts are not being calculated correctly due to not enough decimal places for R. In the CROSS APPLY, R is being CAST to MONEY which only allows for 4 decimal places. It should have at least 8 decimal places. I would recommend using DECIMAL(20, 18). tried changing the datatype to be that but then started getting the following error:
Types don't match between the anchor and the recursive part in column "Balance" of recursive query "Payments".
- For the InterestAPR field I would suggest using DECIMAL(8, 5) instead of FLOAT, though this might be a non-issue if you can get R to be CAST into a DECIMAL(20, 18). But still.
I hope this helps. Take care,
Solomon...
Ah. I couldn't remember specifically what I was doing with respect to rounding. Mine was more a demonstration of the method, and I did not attempt to fine tune it to any specific "mortgage amortization" rules sheet (which I didn't know existed anyway).
Thanks for saying it was an "excellent article" even with such discrepancies. 😀 Glad you felt like being generous today.
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
March 3, 2015 at 3:11 pm
dwain.c (3/1/2015)
Thanks for saying it was an "excellent article" even with such discrepancies. 😀 Glad you felt like being generous today.
Excellent article indeed Dwain. Especially from a guy who called himself "recursively challenged" in your second post HERE just a few short years ago.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 3, 2015 at 3:56 pm
Thank everyone for all the relative insight!
I believe I found a solution with regards to amortizing mulitple loans at once; In the section where you are creating cluster indexes:
CREATE CLUSTERED INDEX IX_#amort_period
ON #amort(period)
I add the following loop:
DECLARE @loan_id INT
SET @loan_id = 1
WHILE @loan_id <= (select max(Loan_ID) from loans)
BEGIN
And it seems to be working like a charm and to the penny in my case. Thanks again for all your help! 🙂
March 3, 2015 at 6:49 pm
henryalugo (3/3/2015)
Thank everyone for all the relative insight!
No problem Henry, I'm glad to see you got it working as needed. I am taking the time to incorporate all of the great hints I got on this one into my dusty old code, and I hope you have done the same.
Now, to all, and this has nothing to do with anything in the code itself, it's just one of those thing that happen to you in life that make you wonder about the nature of coincidence. I hope the mods will forgive an indiscretion if this kind of thing should be posted elsewhere...
I had not thought about this amortization problem since probably about a week after the activity originally died down back in July of 2011. I literally wrote the code to see if I could and forgot about it. Then last Monday, February 23rd, a friend comes to my desk to see if I knew how to calculate a loan schedule. Well, I thought, indeed I believe I do have something that will do that. Imagine my surprise when the very next day I see the email indicating someone had posted to this thread. I know, it would have been a much better story had the two events occurred on the same day, but still, what are the chances?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 4, 2015 at 11:03 am
dwain.c (3/1/2015)
Ah. I couldn't remember specifically what I was doing with respect to rounding. Mine was more a demonstration of the method, and I did not attempt to fine tune it to any specific "mortgage amortization" rules sheet (which I didn't know existed anyway).Thanks for saying it was an "excellent article" even with such discrepancies. 😀 Glad you felt like being generous today.
Hey there. I wouldn't call my complimenting your article "generous" as that implies both a) that it really isn't, and b) that I was somehow incorrect in my assessment, neither of which are true ;-).
Regarding "such discrepancies": had the article been titled, "How to accurately calculate a monthly amortization schedule using a recursive CTE", well, then maybe not "excellent". But the example does show an interesting use case for an rCTE which helps illustrate how those work, regardless of the accuracy of the formula used. Of course, I don't think it would be a bad idea to update the article to state clearly that the formula is for demo purposes only, knowing that enough people just copy / paste stuff and don't validate. But at the same time, people really should do their own validation. Just a thought.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 4, 2015 at 11:29 am
Greg Snidow (3/3/2015)
henryalugo (3/3/2015)
Thank everyone for all the relative insight!No problem Henry, I'm glad to see you got it working as needed. I am taking the time to incorporate all of the great hints I got on this one into my dusty old code, and I hope you have done the same.
Henry: if you are using the code as shown on page 1 of this topic, then that will rarely, if ever, yield a correct amortization schedule.
Greg (and Henry, Dwain, et al.): I have tinkered with the original code (found on page 1) and got it to a working state. I made some additional changes not mentioned previously, especially with regards to:
Set up the test objects and data:
USE [tempdb];
GO
--==== Remove existing objects
IF (OBJECT_ID('tempdb..#LOANS','u') IS NOT NULL)
BEGIN
DROP TABLE #LOANS;
END;
IF (OBJECT_ID('tempdb..#LOAN_CUSTOMERS','u') IS NOT NULL)
BEGIN
DROP TABLE #LOAN_CUSTOMERS;
END;
IF (OBJECT_ID('fnCalculateMonthlyPayment','fn') IS NOT NULL)
BEGIN
DROP FUNCTION dbo.fnCalculateMonthlyPayment;
END;
--==== Create function dbo.fnCalculateMonthlyPayment
GO
CREATE FUNCTION [dbo].[fnCalculateMonthlyPayment]
(
@loan_amt MONEY,
@periods INT,
@per_anum INT,
@rate DECIMAL(8, 5)
)
RETURNS MONEY
WITH SCHEMABINDING -- can't be deterministic without this; needed for computed column to be PERSISTED
AS
BEGIN
DECLARE @calc_rate FLOAT; --DECIMAL(18, 16) does not work so well here
SELECT @calc_rate = (@rate / @per_anum);
RETURN ROUND(@loan_amt * ((@calc_rate * POWER((1 + @calc_rate), @periods)) / (POWER(1 + @calc_rate, @periods) - 1)), 2);
END;
GO
--==== Create and populate a customers table
CREATE TABLE #LOAN_CUSTOMERS
(
CUST_ID INT IDENTITY(1,1) PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(30),
CREATE_DATE DATETIME DEFAULT (GETDATE())
);
INSERT INTO #LOAN_CUSTOMERS (FIRST_NAME, LAST_NAME)
SELECT 'John', 'Doe'
UNION ALL
SELECT 'Jane', 'Buck';
--==== Create and populate a loans table
CREATE TABLE #LOANS
(
LOAN_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
CUST_ID INT NOT NULL, -- If a real table, FK to LOAN_CUSTOMERS (CUST_ID)
LOAN_START_DATE DATE NOT NULL,
LOAN_FIRST_PMT_DATE DATE NOT NULL,
LOAN_PERIODS INT NOT NULL,
LOAN_RATE FLOAT NOT NULL,
LOAN_PER_ANUM INT NOT NULL,
LOAN_AMT MONEY NOT NULL,
LOAN_PMT AS (dbo.fnCalculateMonthlyPayment([LOAN_AMT], [LOAN_PERIODS], [LOAN_PER_ANUM], [LOAN_RATE])) PERSISTED,
LOAN_INTEREST MONEY
);
INSERT INTO #LOANS (CUST_ID, LOAN_START_DATE, LOAN_FIRST_PMT_DATE, LOAN_PERIODS, LOAN_RATE, LOAN_PER_ANUM, LOAN_AMT)
SELECT 1, GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0), 360, 0.05, 12, 200000
UNION ALL
SELECT 2, GETDATE(), DATEADD(MONTH ,DATEDIFF(MONTH, 0, GETDATE()) + 2, 0), 360, 0.06, 12, 188100
UNION ALL
SELECT 2, GETDATE(), DATEADD(MONTH ,DATEDIFF(MONTH, 0, GETDATE()) + 2, 0), 360, 0.03875, 12, 142000;
GO
----------------------------------------------------------------------------------------------------------------
--==== First check for and drop the procedure if it exists
IF (OBJECT_ID('tempdb..#spGetAmortization','p') IS NOT NULL)
BEGIN
DROP PROCEDURE #spGetAmortization;
END;
GO
--==== Start of procedure
CREATE PROCEDURE #spGetAmortization (@loan_id INT)
AS
SET NOCOUNT ON;
--==== Declare the local variables.
DECLARE @payment MONEY,
@Balance MONEY,
@cur_interest MONEY,
@cur_principle MONEY,
@cum_interest MONEY,
@cum_principle MONEY,
@periods INT,
@calc_rate DECIMAL(18, 16)--FLOAT does not work as well here;
--==== Create a temp table for the output of the procedure.
CREATE TABLE #amort
(
LOAN_ID INT NOT NULL,
PERIOD INT NOT NULL,
PMT_DT SMALLDATETIME,
BALANCE MONEY NULL,
PAYMENT MONEY NULL,
CUR_INTEREST MONEY NULL,
CUM_INTEREST MONEY NULL,
CUR_PRINCIPLE MONEY NULL,
CUM_PRINCIPLE MONEY NULL,
CONSTRAINT [PK_#amort] PRIMARY KEY CLUSTERED (LOAN_ID, PERIOD)
)
--==== Set the local variables from the loans table.
SELECT @Balance = LOAN_AMT,
@periods = LOAN_PERIODS,
@payment = LOAN_PMT,
@calc_rate = (LOAN_RATE / LOAN_PER_ANUM),
@cum_interest = 0,
@cum_principle = 0
FROM #LOANS
WHERE loan_id = @loan_id;
--==== First populate a header row for the output. This will be period 0,
-- and will not show any activity
INSERT INTO #amort VALUES (@loan_id, 0, NULL, @Balance, 0, 0, 0, 0, 0);
--==== Now populate a row for each period. The values will be updated later. A loop
-- is used here for those folks who do not have a tally table.
DECLARE @i INT;
SET @i = 1;
WHILE (@i <= @periods)
BEGIN
INSERT INTO #amort
SELECT
LOAN_ID = @loan_id,
PERIOD = @i,
PMT_DT = DATEADD(MONTH, @i - 1, LOAN_FIRST_PMT_DATE),
BALANCE = 0,
PAYMENT = @payment,
CUR_INTEREST = 0,
CUM_INTEREST = 0,
CUR_PRINCIPLE = 0,
CUM_PRINCIPLE = 0
FROM #loans
WHERE LOAN_ID = @loan_id;
SELECT @i = (@i + 1);
END;
--==== Now update the values in the table.
UPDATE a
SET @cur_interest = cur_interest = ROUND(@balance * @calc_rate, 2),
@payment = payment = CASE period WHEN @periods THEN (@cur_interest + @Balance) ELSE payment END,
@cur_principle = cur_principle = CASE period WHEN @periods THEN @Balance ELSE (payment - @cur_interest) END,
@Balance = balance = (@balance - @cur_principle),
@cum_interest = cum_interest = (@cum_interest + @cur_interest),
@cum_principle = cum_principle = (@cum_principle + @cur_principle)
FROM #amort a
WHERE a.PERIOD > 0;
--==== store the calculated Total Interest back in the Loans table
UPDATE ln
SET ln.LOAN_INTEREST = @cum_interest
FROM #LOANS ln
WHERE ln.LOAN_ID = @loan_id;
SELECT * FROM #amort;
GO
Run the examples:
EXEC #spGetAmortization 1;
-- Payment: 1073.64
-- Final Payment: 1076.48
-- Total Interest: 186513.24
EXEC #spGetAmortization 2;
-- Payment: 1127.75
-- Final Payment: 1132.25 (1132.24 when using Bankers' (i.e. ToEven) Rounding)
-- Total Interest: 217894.50 (217894.49 when using Bankers' (i.e. ToEven) Rounding)
EXEC #spGetAmortization 3;
-- Payment: 667.74
-- Final Payment: 665.75
-- Total Interest: 98384.41
-- Matches an actual, bank-provided Amortization Schedule
SELECT * FROM #LOANS;
I believe it is possible to make similar updates to Dwain's rCTE method, but I don't have time (especially not after doing this one) to work through the "type mismatch" issue I ran into.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 4, 2015 at 1:48 pm
Thank you so much Solomon. I never cease to be impressed by the generosity shown by folks here, and their willingness to go above and beyond on someone else's problem.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 4, 2015 at 3:22 pm
Greg Snidow (3/4/2015)
Thank you so much Solomon. I never cease to be impressed by the generosity shown by folks here, and their willingness to go above and beyond on someone else's problem.
You are quite welcome :-). I noticed that I forgot to put in that final UPDATE to store the calculated Total Interest back into the main LOANS table, so I have updated my post above with that logic. I also included a SELECT * FROM #LOANS at the end of the test queries to make it easy to see that it did record the final total value correctly.
Please note that the final payment amount for each of the 3 test cases is not only different from the initially calculated payment amount, but it is sometimes greater than the initial amount and sometimes less.
Please also note that while the stored procedure does allow for passing in a value for "payments per year", the payment date logic only works with monthly (i.e. a value of 12). The dates will not calculate correctly if someone tries to use a value of 24 for bi-monthly payments or a value of 26 for bi-weekly payments.
I should also mention that the second example is also based on a past mortgage that I had that I do have the amortization schedule for. The reason I didn't mention that one matching exactly is that it doesn't exactly match ;-). At least, it doesn't match the updated SQL# function now that I have made the changes to use the correct MONEY (decimal in .NET) datatypes. But, oddly enough, the amortization schedule does match exactly to the current version (3.3.x) of the SQL#[/url] Math_CompoundAmortizationSchedule function, which is using all FLOATs (and I did, years ago, validate the function against another mortgage from another lender. It is a little discouraging that the banks can't even be consistent in this matter. I suppose I would have to have a copy of the function using all FLOATs again to have each way of looking at it. Maybe someday. For now I feel that using DECIMAL / MONEY is more accurate / popular.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
March 4, 2015 at 4:56 pm
Greg Snidow (3/3/2015)
dwain.c (3/1/2015)
Thanks for saying it was an "excellent article" even with such discrepancies. 😀 Glad you felt like being generous today.Excellent article indeed Dwain. Especially from a guy who called himself "recursively challenged" in your second post HERE just a few short years ago.
Bwahahaha! How in the world did you dig up such an ancient thread?!
And while ancient, I do still recall Paul White's unbelievable contribution to it. I think that I've been in awe of him ever since.
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
March 4, 2015 at 5:07 pm
Solomon Rutzky (3/4/2015)
dwain.c (3/1/2015)
Ah. I couldn't remember specifically what I was doing with respect to rounding. Mine was more a demonstration of the method, and I did not attempt to fine tune it to any specific "mortgage amortization" rules sheet (which I didn't know existed anyway).Thanks for saying it was an "excellent article" even with such discrepancies. 😀 Glad you felt like being generous today.
Hey there. I wouldn't call my complimenting your article "generous" as that implies both a) that it really isn't, and b) that I was somehow incorrect in my assessment, neither of which are true ;-).
Regarding "such discrepancies": had the article been titled, "How to accurately calculate a monthly amortization schedule using a recursive CTE", well, then maybe not "excellent". But the example does show an interesting use case for an rCTE which helps illustrate how those work, regardless of the accuracy of the formula used. Of course, I don't think it would be a bad idea to update the article to state clearly that the formula is for demo purposes only, knowing that enough people just copy / paste stuff and don't validate. But at the same time, people really should do their own validation. Just a thought.
Take care,
Solomon..
Well thanks again.
I wrote that article to serve two purposes:
1. To try to get past being recursively challenged.
2. To try to illustrate some examples of recursion in SQL without falling back upon the tired old hierarchy traversal that everybody and his brother was posting to their blogs (often copied directly from the BOL page).
Perhaps you're right about the update, if I can ever find the time.
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
March 4, 2015 at 7:53 pm
dwain.c (3/4/2015)
Bwahahaha! How in the world did you dig up such an ancient thread?!And while ancient, I do still recall Paul White's unbelievable contribution to it. I think that I've been in awe of him ever since.
The memory is like a steel trap Dwain. I wish. No, your second statement pretty much answers your question. Like you, Paul's most excellent contribution really opened my eyes to the world of recursion, so that's just one of those threads I remember, and I remember everything about it, including your statement. So now this thread will probably also be added to the mental list. That list now has 3 whole threads on it 🙂
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 7, 2015 at 1:40 am
Jeff Moden (7/5/2011)
If you notice, the final entry didn't really get as close to zero as bankers and little ol' ladies with umbrellas would like. 😛
Man, expand the boundary of the column in SSMS to reveal all the numbers!
It's -1.67119651450776E-09
How much closer to zero those ladies with umprellas want it to be???
:hehe:
I know you're well familiar with math rules (I mean math, not arithmetics).
If you think about it - FLOAT holds 15 true digits.
Arithmetic operations (+, -) may cause losing 1 true digit per 3 consequitive operations, we have 5 such operastions in the function calculating [payment], and then another 1 adding it up. So, we might expect to lose 2 digits.
Having 200k loan we stay within 6 + 2 (or 4 if you want to use money type) digits, so wi still must have extra 3 precise digits to our service.
These thought made me thing about something really silly what could cause that "disbalance".
And yes - after checking BALANCE + CUM_PRINCIPLE (exactly 200000.0000 for every row) I was brought strait to the columns width in SSMS. :w00t:
Turns out, in this case we still manage to maintain 5+9 = 14 digits precision after all calculations. Not bad for imprecise data type.:-)
To avoid confusion - do not mess with digital types during calculations.
Just convert the final numbers to MONEY for presentation purposes:
SELECT LOAN_ID, PERIOD, PMT_DT,
CONVERT(money, BALANCE),
CONVERT(money, PAYMENT),
CONVERT(money, CUR_INTEREST),
CONVERT(money, CUM_INTEREST),
CONVERT(money, CUR_PRINCIPLE),
CONVERT(money, CUM_PRINCIPLE )
FROM #amort
_____________
Code for TallyGenerator
March 8, 2015 at 2:29 pm
Sergiy (3/7/2015)
Jeff Moden (7/5/2011)
If you notice, the final entry didn't really get as close to zero as bankers and little ol' ladies with umbrellas would like. 😛Man, expand the boundary of the column in SSMS to reveal all the numbers!
It's -1.67119651450776E-09
How much closer to zero those ladies with umprellas want it to be???
:hehe:
Not sure why it didn't work out that way on my box. Heh... and no... it wasn't due to limited column width. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 8, 2015 at 4:42 pm
Jeff Moden (3/1/2015)
Solomon Rutzky (3/1/2015)
Interestingly enough, nearly all of them were wrong in the exact same way. They all did their rounding at the presentation layer.I'm not so sure that's the wrong method.
It is definitely the wrong method.
It looks to me very much as if the main problem is not following the standard financial rule of rounding at every point at which a balance has to be (or has been) stated. At least that's how it's done this side of the pond. Things measured as amounts of money are inherently discrete, not continuous, and whenever they need to be made visible they must be rounded to one of the discrete values; and all subsequent computation should be based on the rounded values, since if it isn't those displayed values would be misleading. So going to decimal(38,20) will deliver bad results if interest is charged monthly but the calculation is done at that precision for the whole year loan period or for each whole year instead of the balance being rounded to whatever the discrete nature of the quantity requires at each month. If computations within a month are sufficiently complex to require greater precision to be used than for the month end figures, there has to be an explicit rounding operation at the end of each months calculations. Ideally the precision required for internal computation is identical to that required for display, so that perhaps decimal(28,2) or decimal (26,4) can be used for the whole thing (depending on the currency), but sometimes we find ourselves not in an ideal world and have to use different precisions for computation and for display - and if we then forget to replace the computed values by the displayed values before doing further calculation we get wrong answers - maybe predictions are different from actuals (that can be a disaster for a fixed interest mortgage quotation, for example).
Tom
March 8, 2015 at 5:49 pm
TomThomson (3/8/2015)
Jeff Moden (3/1/2015)
Solomon Rutzky (3/1/2015)
Interestingly enough, nearly all of them were wrong in the exact same way. They all did their rounding at the presentation layer.I'm not so sure that's the wrong method.
It is definitely the wrong method.
It looks to me very much as if the main problem is not following the standard financial rule of rounding at every point at which a balance has to be (or has been) stated. At least that's how it's done this side of the pond. Things measured as amounts of money are inherently discrete, not continuous, and whenever they need to be made visible they must be rounded to one of the discrete values; and all subsequent computation should be based on the rounded values, since if it isn't those displayed values would be misleading. So going to decimal(38,20) will deliver bad results if interest is charged monthly but the calculation is done at that precision for the whole year loan period or for each whole year instead of the balance being rounded to whatever the discrete nature of the quantity requires at each month. If computations within a month are sufficiently complex to require greater precision to be used than for the month end figures, there has to be an explicit rounding operation at the end of each months calculations. Ideally the precision required for internal computation is identical to that required for display, so that perhaps decimal(28,2) or decimal (26,4) can be used for the whole thing (depending on the currency), but sometimes we find ourselves not in an ideal world and have to use different precisions for computation and for display - and if we then forget to replace the computed values by the displayed values before doing further calculation we get wrong answers - maybe predictions are different from actuals (that can be a disaster for a fixed interest mortgage quotation, for example).
Thanks for chiming in Tom. I was wondering whether rounding rules on an amortization may depend on local jurisdiction. In that case, it would be interesting to try to identify the differences, but probably not an exercise you'd want to code to. Just stick with the local rules.
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 15 posts - 31 through 45 (of 97 total)
You must be logged in to reply to this topic. Login to reply