Automatically update interest cost each time database opened
I have an SQl datatabase that has an "Interest" column that shows the opportunity interest cost incurred on an investment. That is, the interest that would have been received if the amount invested had been invested in a term deposit. As each day passes, the opportunity cost increases. I have a C# front end that gets all the data into the database. My problem is that each time I open the database in C# gridview I want the opportunity interest cost to automatically update - that is to increase the interest cost to reflect the additional time that the investment has been made. I a novice in and am looking for some pointers as to an approach, book or method or some SQL terms that I should look at for coming up with a solution.
You can include a calculated column in your query. So you could do something like this: select Name , DateAcquired , Value , InterestAPR , Value * ((InterestAPR / 365) * datediff(day, DateAcquired, getdate()) as OpportunityInterestCost from dbo.Asset order by DateAcquired , Name;