Creating Compound Interest Calculations in SQL

Question

Creating Compound Interest Calculations in SQL

Adam Aspin

SSCarpal Tunnel

Points: 4900
More actions
July 5, 2018 at 12:02 am

#354338

Comments posted to this topic are about the item Creating Compound Interest Calculations in SQL

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

darius.england Mr or Mrs. 500 Points: 557 More actions · Answer 1

Hi there. Good article... but please, please, please, when you write SQL and alias tables, as it should be, also alias the columns used! It's absolutely frustrating and highly unhelpful to the readers when you pull out fields from tables without specifying clearly in the query itself from which table they come.

douglas.forde SSC Veteran Points: 204 More actions · Answer 2

Could you not achieve the same output by running the SRC query into a temporary table and using that to produce the results?

Like this:

SELECT DATEDIFF(mm, DateBought, SL.SaleDate) AS MonthsSincePurchase, (Cost + PartsCost + RepairsCost) AS InitialCost INTO # temp1 FROM Data.Stock SK INNER JOIN Data.SalesDetails SD ON SK.StockCode = SD.StockID INNER JOIN Data.Sales SL ON SL.SalesID = SD.SalesID WHERE DATEDIFF(mm, DateBought, SL.SaleDate) > 2

SELECT InitialCost, MonthsSincePurchase, (InitialCost * POWER(1 + (0.75 / 100), MonthsSincePurchase)) - InitialCost AS InterestCharge, InitialCost * POWER(1 + (0.75 / 100), MonthsSincePurchase) AS TotalWithInterestFROM#temp1

Is there a benefit by doing it using the method in the article?

jer.roe SSC Journeyman Points: 90 More actions · Answer 3

"why not use a temp table and do it in steps"
a. the formulas can be broken out in to temp tables. In some cases however you may not want to create two tables, or the data sets could be rather large (millions/billion rows)
b. the subquery works, it can be difficult to read bottom up.... often we prefer to use nested CTE so you can read "top down". I don't believe there is performance difference between CTE or nested subqueries.

;with CTE_step1 as
(select a+1 as b)

, CTE_step2 as
(b+1) as c

select *
,c *2 = d
from CTE_step2

Japie Botma SSCrazy Points: 2990 More actions · Answer 4

For me the first mistake is that there should be an OUTER JOIN with sales with ISNULL(SL.SaleDate, GETDATE()) AS SaleDate, because not all stock are sold yet. (It never is THAT good. 🙂 )

5ilverFox
Consulting DBA / Developer
South Africa

Ray Herring SSCertifiable Points: 5533 More actions · Answer 5

I realize it was not the focus of your article but as a curious question, how would you really handle the dates?
First, if the car is still in stock wouldn't the "SaleDate" be Null? If so then the logic needs to change quite a bit. Perhaps the manager is interested in both the unsold and recently sold or some other rule but in any event it's a hole that needs to be addressed.

Second, from BOL, the DateDiff function "Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate" which is not precisely the number of months elapsed.

Would a calculation like this be only interested in whole calendar months or would it be interested in the number of 30 day intervals, or perhaps some other trigger?

Say the DateBought is the 31st of the March and the current date (or SaleDate) is the 1st of May. Is that one month, two months, 3 months, ...?

Just curious 😉