Update Statement in Table - Please urgent help required...

[mayur123creation]

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.

[mayur123creation]

-- Prepare sample data
set dateformat mdy

declare @marketvalue table (id int primary key, open_date datetime, value money, fees money)

insert @marketvalue (id, open_date, value)
select 1, '05/05/06', 33456.07 union all
select 2, '01/25/03', 24256.40 union all
select 3, '03/03/07', 45657.23 union all
select 4, '09/29/01', 5672.89 union all
select 5, '05/15/07', 21345.89

-- Stage the dataUPDATE m
SET m.Fees = m.Value * d.FinalRate
FROM @MarketValue AS m
INNER JOIN (
SELECT ID,
(
CASE
WHEN First >= 2.0 THEN 0.05
WHEN First >= 1.0 THEN 0.10
WHEN First >= 0.0 THEN 0.20
ELSE 0
END +
CASE
WHEN Second >= 2.0 THEN 0.05
WHEN Second >= 1.0 THEN 0.10
WHEN Second >= 0.0 THEN 0.20
ELSE 0
END +
CASE
WHEN Third >= 2.0 THEN 0.05
WHEN Third >= 1.0 THEN 0.10
WHEN Third >= 0.0 THEN 0.20
ELSE 0
END
) / 3.0 AS FinalRate
FROM (
SELECT ID,
DATEDIFF(DAY, open_date, '04/15/07') / 365.0 AS First,
DATEDIFF(DAY, open_date, '05/15/07') / 365.0 AS Second,
DATEDIFF(DAY, open_date, '06/15/07') / 365.0 AS Third
FROM @MarketValue
) AS q
) AS d ON d.ID = m.ID

-- Show the expected output
SELECT * FROM @MarketValue

[azimmer]

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.

I suggest that you create a cursor (see more details in help at DECLARE CURSOR) for each row of the table, open and iterate the cursor with fetch next and checking @@FETCH_STATUS, and put your calculation in the body of your iteration (WHILE cycle).