Creating Compound Interest Calculations in SQL

  • Adam Aspin

    SSCarpal Tunnel

    Points: 4795

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

  • darius.england

    SSC-Addicted

    Points: 486

    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 Enthusiast

    Points: 193

    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

    "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: 2897

    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: 5207

    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 😉

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

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