Hi to All Experts....
I am new in DB side. I need to update all rows for column (fees) in table (MarketValue). This table has around 1500 rows.
TB : MarketValue has following colums
id - int (pk)
open_date - datetime
value - money
fees - money
select * from MarketValue
id ope_date value fees
1 05/05/06 33456.07
2 01/25/03 24256.40
3 03/03/07 45657.23
4 09/29/01 5672.89
5 05/15/07 21345.89
.....
.....
Here I need to update fees as per following condition
@first_mnt, @second_mnt, @third_mnt are decimal
@first_mnt = select cast(('04/15/07' - open_dt) as decimal) / 365 from marketvalue
@second_mnt = select cast(('05/15/07' - open_dt) as decimal) / 365 from marketvalue
@third_mnt = select cast(('06/15/07' - open_dt) as decimal) / 365 from marketvalue
/* THIRD MONTH */
if @third_mnt >= 2
@rate3 = 0.05
if @third_mnt >= 1 and @third_mnt < 2
@rate3 = 0.10
if @third_mnt >= 0 and @third_mnt < 1
@rate3 = 0.20
/* SECOND MONTH */
if @second_mnt >= 2
@rate2 = 0.05
if @second_mnt>= 1 and @second_mnt < 2
@rate2 = 0.10
if @second_mnt >= 0 and @second_mnt < 1
@rate2 = 0.20
/* FIRST MONTH */
if @first_mnt >= 2
@rate1 = 0.05
if @first_mnt >= 1 and @first_mnt < 2
@rate1 = 0.10
if @first_mnt >= 0 and @first_mnt < 1
@rate1 = 0.20
@final_rate = (@rate3 + @rate2 + @rate1) / 3
Update MarketValue
set fees = (value * @final_rate) - for each row in table.
As I have mentioned I am new in DB side I don't know how to this calculation in Stored Procedure. I am trying from last few days but could not able to pen proper code for above equation. All rows has to go through this equation.
Any help will be appreciated.