March 25, 2021 at 4:41 pm
So, long story short... A conversation about loops, cursors and in-line functions ended up with me proclaiming that I could could do a full payment level, loan amortization schedule using only set based t-sql (famous last words).
My attempt to cheat by finding something pre-rolled on Google fell flat and was forced to actually do the work myself, based on standard TVM equations.
I ended up liking the solution and figured that I should share it with the SSC hive mind and see what improvements come out of the conversation...
DECLARE
@n int = 360,-- total # of payments
@ir float = .05,-- anual interest rate (note: enter as a decimal... So %5 would be entered as 0.05...)
@pv float = 225000.00,-- present value (original loan amount)
@npy int = 12,-- # of periods per year
@beg_dt date = GETDATE(); -- the date of the first payment.
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_Tally (n) AS (
SELECT TOP (@n)
ROW_NUMBER() OVER (ORDER BY a.n)
FROM
cte_n2 a CROSS JOIN cte_n2 b
ORDER BY
a.n
)
SELECT
pmt_num = t.n,
pd.payment_date,
beg_ballance = CONVERT(decimal(19,2), pv.beg_ballance),
scheduled_pmt = CONVERT(decimal(19, 2), pmt.pmt_calc),
amt_to_intrest = CONVERT(decimal(19,2), ipmt.ipmt),
amt_to_principal = CONVERT(decimal(19,2), ppmt.ppmt),
end_ballance = CONVERT(decimal(19,2), pv.end_ballance)
FROM
cte_Tally t
CROSS APPLY ( VALUES (@pv / (POWER(1 + (@ir / @npy), @n) - 1) * ((@ir / @npy) * POWER(1 + (@ir / @npy), @n))) ) pmt (pmt_calc)
CROSS APPLY ( VALUES (
ABS(-@pv * POWER(1 + (@ir / @npy), t.n-1) + pmt.pmt_calc * (POWER(1 + (@ir / @npy), t.n-1) -1) / (@ir / @npy)),
ABS(-@pv * POWER(1 + (@ir / @npy), t.n) + pmt.pmt_calc * (POWER(1 + (@ir / @npy), t.n) -1) / (@ir / @npy))
) ) pv (beg_ballance, end_ballance)
CROSS APPLY ( VALUES (pv.beg_ballance * (@ir / @npy)) ) ipmt (ipmt)
CROSS APPLY ( VALUES (pmt.pmt_calc - ipmt.ipmt) ) ppmt (ppmt)
CROSS APPLY ( VALUES (
CASE
WHEN @npy <= 12 THEN DATEADD(MONTH, (12 / @npy) * (t.n - 1), @beg_dt)
WHEN @npy = 26 THEN DATEADD(WEEK, 2 * (t.n - 1), @beg_dt)
ELSE DATEADD(DAY, (365 / @npy) * (t.n - 1), @beg_dt)
END
) ) pd (payment_date);
March 25, 2021 at 8:08 pm
Just one thought - you can simplify the calculations. I used 'generic' names - which you probably want to change to what they actually represent...but something like this:
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_Tally (n) AS (
SELECT TOP (@n)
ROW_NUMBER() OVER (ORDER BY a.n)
FROM
cte_n2 a CROSS JOIN cte_n2 b
ORDER BY
a.n
)
SELECT
pmt_num = t.n,
pd.payment_date,
beg_ballance = CONVERT(decimal(19,2), pv.beg_ballance),
scheduled_pmt = CONVERT(decimal(19, 2), pmt.pmt_calc),
amt_to_intrest = CONVERT(decimal(19,2), ipmt.ipmt),
amt_to_principal = CONVERT(decimal(19,2), ppmt.ppmt),
end_ballance = CONVERT(decimal(19,2), pv.end_ballance)
FROM
cte_Tally t
CROSS APPLY ( VALUES (@ir / @npy)) As v(var1)
CROSS APPLY ( VALUES (POWER(1 + v.var1, @n), POWER(1 + v.var1, t.n), POWER(1 + v.var1, t.n-1) )) As e(calc1, calc2, calc3)
CROSS APPLY ( VALUES (@pv / (e.calc1 - 1) * (v.var1 * e.calc1)) ) As pmt (pmt_calc)
CROSS APPLY ( VALUES (ABS(-@pv * e.calc3 + pmt.pmt_calc * (e.calc3 - 1) / v.var1)
, ABS(-@pv * e.calc2 + pmt.pmt_calc * (e.calc2 - 1) / v.var1) )) As pv (beg_ballance, end_ballance)
CROSS APPLY ( VALUES (pv.beg_ballance * v.var1) ) As ipmt (ipmt)
CROSS APPLY ( VALUES (pmt.pmt_calc - ipmt.ipmt) ) As ppmt (ppmt)
CROSS APPLY ( VALUES (CASE WHEN @npy <= 12 THEN DATEADD(MONTH, (12 / @npy) * (t.n - 1), @beg_dt)
WHEN @npy = 26 THEN DATEADD(WEEK, 2 * (t.n - 1), @beg_dt)
ELSE DATEADD(DAY, (365 / @npy) * (t.n - 1), @beg_dt)
END) ) As pd (payment_date);
And - you can simplify even further by using fnTally instead.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 25, 2021 at 8:26 pm
Just one thought - you can simplify the calculations. I used 'generic' names - which you probably want to change to what they actually represent...but something like this:
And - you can simplify even further by using fnTally instead.
Cool Idea, I like it. I'll play with it and see it impacts performance and/or compile time. As a general rule I try to avoid nesting functions within functions , so that was the reason for building the tally in line rather than a function.
March 25, 2021 at 8:41 pm
Jeffrey Williams wrote:Just one thought - you can simplify the calculations. I used 'generic' names - which you probably want to change to what they actually represent...but something like this:
And - you can simplify even further by using fnTally instead.
Cool Idea, I like it. I'll play with it and see it impacts performance and/or compile time. As a general rule I try to avoid nesting functions within functions , so that was the reason for building the tally in line rather than a function.
I do the same thing - either way works and probably won't see any differences in performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 26, 2021 at 12:31 am
I do the same thing - either way works and probably won't see any differences in performance.
I found from personal experience that function performs better than CTE.
Probably, saving on compilation time.
_____________
Code for TallyGenerator
March 26, 2021 at 1:42 pm
Jeffrey Williams wrote:I do the same thing - either way works and probably won't see any differences in performance.
I found from personal experience that function performs better than CTE.
Probably, saving on compilation time.
I guess that depends on how each is written and used - both are incorporated into the final query and then SQL generates the execution plan. I have found that if both are doing the same thing we end up with the same execution plan and no difference in performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 26, 2021 at 5:01 pm
Nice work, and a fun solution, Jason.
Just a couple of comments.
I'm curious if it would save CPU cycles is to make the interest rate per period a variable. I'm not sure if the algebrizer(?) converts @ir/@pir to an expression one time or for every row.
DECLARE @PIR float = (@IR/@NPY)
Also I couldn't help but notice that (through the wonders of floating point math) in the second month of the schedule:
beg_ballance = 224729.65
amt_to_principal = 271.48
end_balance = 224458.18 , NOT .17
It's a tiny thing. I'm sure it doesn't always round in the bank's favor. After 30 years I would only expect it to add up to a dollar or so difference in total payments. I'm sure it's an accepted practice. It just bugs me.
Finally, I rewrote this as a recursive CTE. It is not attached but runs slower than your solution (as expected). It also calculates principle as simply payment - interest and ending balance simply as beginning balance - principal, so the results vary slightly. This is what caused me to notice the penny discrepancies.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 26, 2021 at 5:49 pm
Nice work, and a fun solution, Jason.
Just a couple of comments.
I'm curious if it would save CPU cycles is to make the interest rate per period a variable. I'm not sure if the algebrizer(?) converts @ir/@pir to an expression one time or for every row.
DECLARE @PIR float = (@IR/@NPY)Also I couldn't help but notice that (through the wonders of floating point math) in the second month of the schedule:
beg_ballance = 224729.65 amt_to_principal = 271.48 end_balance = 224458.18 , NOT .17
It's a tiny thing. I'm sure it doesn't always round in the bank's favor. After 30 years I would only expect it to add up to a dollar or so difference in total payments. I'm sure it's an accepted practice. It just bugs me.
Finally, I rewrote this as a recursive CTE. It is not attached but runs slower than your solution (as expected). It also calculates principle as simply payment - interest and ending balance simply as beginning balance - principal, so the results vary slightly. This is what caused me to notice the penny discrepancies.
Thank you for the feedback... As far as I know, the algebrizer does the evaluation just the one time. But even if it doesn't, adding the separate DECLARE statement would prevent me from using it in an iTVF.
As far as the float data types... I hate it but the POWER() function uses floats and can throw errors if you feed it other numeric data types.
As far as rounding errors, I feel pretty good about the current results. As it stands, I'm not doing any rounding or truncating until the final select columns. I've compared the output to other schedule calculators and it appears to be spot on. That said, anytime you're dealing with fractions of a penny, rounding is going to be a potential issue.
March 26, 2021 at 6:01 pm
Understand completely. I really don't consider the rounding issue to be an error. Just mildly annoying. Please don't take anything I said as being critical !! I like your solution.
Regarding the algebrizer, I know it creates the expression once. What I wasn't sure about was whether or not it evaluates that expression for every row, or whether it was smart enough to realize that the expression would never change from row to row. If it evaluated the expression every time, it would amount to 360 division operations instead of just one. Just curious, and I'm not expecting you to research it for me.
Again, nice work.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 26, 2021 at 6:20 pm
Understand completely. I really don't consider the rounding issue to be an error. Just mildly annoying. Please don't take anything I said as being critical !! I like your solution.
Regarding the algebrizer, I know it creates the expression once. What I wasn't sure about was whether or not it evaluates that expression for every row, or whether it was smart enough to realize that the expression would never change from row to row. If it evaluated the expression every time, it would amount to 360 division operations instead of just one. Just curious, and I'm not expecting you to research it for me.
Again, nice work.
Thank you for the kind words. No, I didn't read any negativity into any of your comments. The fact is that I posted my solution for two reasons. #1 Make available to anyone who would benefit from it and #2 get critical feedback from the SSC brain trust. I've learned over the years that my first idea isn't always the best idea.
Hopefully my response to the earlier post didn't give the impression that I took your comments as anything other than legitimate feedback.
March 26, 2021 at 6:38 pm
If you are interested - SQL generates the following code for Jason's original version:
[Expr1056] = Scalar Operator(CASE WHEN [@npy]<=(12) THEN dateadd(month,CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(bigint,(12)/[@npy],0)*([Expr1050]-(1)),0),[@beg_dt]) ELSE CASE WHEN [@npy]=(26) THEN dateadd(week,CONVERT_IMPLICIT(int,(2)*([Expr1050]-(1)),0),[@beg_dt]) ELSE dateadd(day,CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(bigint,(365)/[@npy],0)*([Expr1050]-(1)),0),[@beg_dt]) END END), [Expr1057] = Scalar Operator(CONVERT(decimal(19,2),abs( -([@pv]*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0)))+[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0)))*(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0))-(1.0000000000000000e+000))/([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0))),0)), [Expr1058] = Scalar Operator(CONVERT(decimal(19,2),[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))),0)), [Expr1059] = Scalar Operator(CONVERT(decimal(19,2),abs( -([@pv]*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0)))+[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0)))*(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0))-(1.0000000000000000e+000))/([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)),0)), [Expr1060] = Scalar Operator(CONVERT(decimal(19,2),[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0)))-abs( -([@pv]*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0)))+[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0)))*(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0))-(1.0000000000000000e+000))/([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)),0)), [Expr1061] = Scalar Operator(CONVERT(decimal(19,2),abs( -([@pv]*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050],0)))+[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0)))*(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050],0))-(1.0000000000000000e+000))/([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0))),0))
And this is the generated code from my version:
[Expr1060] = Scalar Operator(CASE WHEN [@npy]<=(12) THEN dateadd(month,CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(bigint,(12)/[@npy],0)*([Expr1050]-(1)),0),[@beg_dt]) ELSE CASE WHEN [@npy]=(26) THEN dateadd(week,CONVERT_IMPLICIT(int,(2)*([Expr1050]-(1)),0),[@beg_dt]) ELSE dateadd(day,CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(bigint,(365)/[@npy],0)*([Expr1050]-(1)),0),[@beg_dt]) END END), [Expr1061] = Scalar Operator(CONVERT(decimal(19,2),abs( -([@pv]*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0)))+[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0)))*(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0))-(1.0000000000000000e+000))/([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0))),0)), [Expr1062] = Scalar Operator(CONVERT(decimal(19,2),[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))),0)), [Expr1063] = Scalar Operator(CONVERT(decimal(19,2),abs( -([@pv]*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0)))+[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0)))*(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0))-(1.0000000000000000e+000))/([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)),0)), [Expr1064] = Scalar Operator(CONVERT(decimal(19,2),[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0)))-abs( -([@pv]*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0)))+[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0)))*(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050]-(1),0))-(1.0000000000000000e+000))/([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)),0)), [Expr1065] = Scalar Operator(CONVERT(decimal(19,2),abs( -([@pv]*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050],0)))+[@pv]/(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0))-(1.0000000000000000e+000))*([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0)*power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[@n],0)))*(power((1.0000000000000000e+000)+[@ir]/CONVERT_IMPLICIT(float(53),[@npy],0),CONVERT_IMPLICIT(float(53),[Expr1050],0))-(1.0000000000000000e+000))/([@ir]/CONVERT_IMPLICIT(float(53),[@npy],0))),0))
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 26, 2021 at 7:23 pm
Forget I said anything about making interest rate per period a variable. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 29, 2021 at 4:07 pm
Back in the old days, we attend lookup tables for this kind of stuff. Since SQL is designed for looking up data in tables, instead of doing recursive loops and fancy control flow, why not just take advantage of the fact that this is a declarative language and download such a table off the Internet? Actually, I bet you can find such things in the accounting department in spreadsheets. This would guarantee that the database and the accounting departments are actually in sync!
Please post DDL and follow ANSI/ISO standards when asking for help.
March 29, 2021 at 4:17 pm
Back in the old days, we attend lookup tables for this kind of stuff. Since SQL is designed for looking up data in tables, instead of doing recursive loops and fancy control flow, why not just take advantage of the fact that this is a declarative language and download such a table off the Internet? Actually, I bet you can find such things in the accounting department in spreadsheets. This would guarantee that the database and the accounting departments are actually in sync!
I am not sure that would work anymore - in the 'old' days we had fixed interest rates and terms and we could perform a lookup. Now - we have terms that are adjustable, payment schedules that are adjustable (e.g. twice a month, every 2 weeks, once a month) - and most importantly the ability to buy down the interest with larger down payments and specials.
Not sure how a lookup table could be generated to account for all scenarios - but I would definitely be interested to see that table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 29, 2021 at 5:49 pm
Back in the old days, we attend lookup tables for this kind of stuff. Since SQL is designed for looking up data in tables, instead of doing recursive loops and fancy control flow, why not just take advantage of the fact that this is a declarative language and download such a table off the Internet? Actually, I bet you can find such things in the accounting department in spreadsheets. This would guarantee that the database and the accounting departments are actually in sync!
Joe, Please go back to my original code and point out where you see "recursive loops and fancy control flow"...
Referencing a table would completely negate the entire point of the exercise. To prove to another DBA that I could use math and a tally table to calculate a full amortization schedule without using loops, cursors or recursion. The fact is, I didn't need an amortization function, I needed to prove a point.
The point being that, at it's core, this is a math problem. Storing pre-calculated values makes about as much sense as storing multiplication tables. In the old days, pre-calculated tables made sense because doing the calculations by hand would be complex, time consuming and error prone. It's the same reason we had natural log tables. Modern computing has made them irrelevant.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply