Complex Update Statement in Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex Update Statement in Table

Author

Topic

mayurcreation
Starting Member

16 Posts

Posted - 2007-08-21 : 10:35:43

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_qtr, @second_qtr, @third_qtr are decimal

@first_qtr = select cast(('04/15/07' - open_dt) as decimal) / 365 from marketvalue
@second_qtr = select cast(('05/15/07' - open_dt) as decimal) / 365 from marketvalue
@third_qtr = select cast(('06/15/07' - open_dt) as decimal) / 365 from marketvalue

-- THIRD QUARTER --
if @third_qtr >= 2
@rate3 = 0.05

if @third_qtr >= 1 and @third_qtr < 2
@rate3 = 0.10

if @third_qtr >= 0 and @third_qtr < 1
@rate3 = 0.20

-- sECOND QUARTER --
if @second_qtr >= 2
@rate2 = 0.05

if @second_qtr >= 1 and @second_qtr < 2
@rate2 = 0.10

if @second_qtr >= 0 and @second_qtr < 1
@rate2 = 0.20

-- FIRST QUARTER --
if @first_qtr >= 2
@rate1 = 0.05

if @first_qtr >= 1 and @first_qtr < 2
@rate1 = 0.10

if @first_qtr >= 0 and @first_qtr < 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 write proper code for above equation.

Any help will be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 10:49:04

See this topic if it applies to your business rules
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88085

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 10:51:14

Also, you are talking about QUARTER, but the formulas are about YEARS.

E 12°55'05.25"
N 56°04'39.16"

mayurcreation
Starting Member

16 Posts

Posted - 2007-08-21 : 11:01:07

Fees should be paid Quarterly and to calculate fees I need to see how old the investment is. 1st year 20% fees, 2nd year 10% fees and 3rd and beyond 5% fees. As said before fees will be paid quarterly.

Any suggestion or help will be appreciated. Once again thanks for your time.

quote:
Originally posted by Peso

Also, you are talking about QUARTER, but the formulas are about YEARS.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 11:19:41

[code]-- 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[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 11:22:13

Result from above suggestion is

id  open_date	value     fees
--  ----------  --------  ---------
 1  2006-05-05  33456.07  4460.7982
 2  2003-01-25  24256.40  1212.8200
 3  2007-03-03  45657.23  9131.4460
 4  2001-09-29   5672.89   283.6445
 5  2007-05-15  21345.89  2846.1116

E 12°55'05.25"
N 56°04'39.16"

mayurcreation
Starting Member

16 Posts

Posted - 2007-08-21 : 11:44:31

EXCELLENT WORK.....

I could not belive this equation can be so simple. You are master mind.

Many Thanks for your great help.
Mayur

quote:

Originally posted by Peso

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

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources