Multiply Txn or value with rate or percentage

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

General SQL Server Forums

New to SQL Server Programming

Multiply Txn or value with rate or percentage

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-06-11 : 23:08:38

Hi, i have 2 table.
--TblTxn_Value
--TblRate_Percentage

1.TblTxn_Value

TblTxn_ValueID TblTxn TblValue TblTxn_ValueItemID Commission SubID
--------------------------------------------------------------------
1 50 450 Item1 KO
2 15 100 Item1 LO
3 10 300 Item2 MI
4 25 450 Item2 HE
5 50 455 Item3 XX
6 50 458 Item4 XX

2.TblRate_Percentage

TblRate_PercentageID Rate_Percent_Value IsRate SubID
---------------------------------------------------------------
Item1 3.75 Y XX
Item2 80 N XX
Item3 70 N PP
Item4 1.07 Y PP
Item5 2.53 Y XX
Item6 1.75 Y PP

My question is How to i update my commission column from table 1?
The commission formula as below.

1. Commission = TblTxn * Rate_Percent_Value (if IsRate ='Y')
2. Commission = TblValue * Rate_Percent_Value / 100 (if IsRate ='N')

Remarks :
1. PP from table2 = KO, LO, MI, HE from table1
2. XX from table2 = XX only
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-11 : 23:13:52

[code]
select t.*,
commission = t.TblTxn * case when r.IsRate = 'Y' then r.Rate_Percent_Value
else r.Rate_Percent_Value / 100
end
from TblTxn_Value t
inner join TblRate_Percentage r on t.TblTxn_ValueItemID = r.TblRate_PercentageID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-06-12 : 02:46:33

quote:

Originally posted by khtan


select t.*,
       commission = t.TblTxn * case when r.IsRate = 'Y' then r.Rate_Percent_Value
                                    else r.Rate_Percent_Value / 100
                                    end
from   TblTxn_Value t
       inner join TblRate_Percentage r    on   t.TblTxn_ValueItemID = r.TblRate_PercentageID

KH
[spoiler]Time is always against us[/spoiler]

Hi KH Tan,

Thanks your reply.
But how to i use update and set to my commission?
I write

Update TblTxn_Value
Set commission =

select t.*,
commission = t.TblTxn * case when r.IsRate = 'Y' then r.Rate_Percent_Value
else r.Rate_Percent_Value / 100
end
from TblTxn_Value t
inner join TblRate_Percentage r on t.TblTxn_ValueItemID = r.TblRate_PercentageID

where .........???

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 02:54:26

[code]
update t
set
~~select t.*,~~
commission = t.TblTxn * case when r.IsRate = 'Y' then r.Rate_Percent_Value
else r.Rate_Percent_Value / 100
end
from TblTxn_Value t
inner join TblRate_Percentage r on t.TblTxn_ValueItemID = r.TblRate_PercentageID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-06-12 : 03:19:45

quote:

Originally posted by khtan


update t
set
select t.*,
       commission = t.TblTxn * case when r.IsRate = 'Y' then r.Rate_Percent_Value
                                    else r.Rate_Percent_Value / 100
                                    end
from   TblTxn_Value t
       inner join TblRate_Percentage r    on   t.TblTxn_ValueItemID = r.TblRate_PercentageID

KH
[spoiler]Time is always against us[/spoiler]

Thanks KH Tan.
It works and it is what i want. Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 03:20:20

welcome

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-12 : 05:11:25

For accuracy, use 100.0 instead of 100
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-12 : 05:16:52

quote:
Originally posted by madhivanan

For accuracy, use 100.0 instead of 100
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

don't need that. The rate is already a decimal number

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-12 : 08:54:29

quote:
Originally posted by khtan

quote:
Originally posted by madhivanan

For accuracy, use 100.0 instead of 100
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

don't need that. The rate is already a decimal number

KH
[spoiler]Time is always against us[/spoiler]

Ok. I missed to note that

Madhivanan

Failing to plan is Planning to fail

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-06-24 : 05:28:58

Hi Everyone,

there are additional column on the 2 table which i highlighted with red color.

How is the new update statement look like?
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources