Calculate commission and update set 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

General SQL Server Forums

New to SQL Server Programming

Calculate commission and update set table

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-06-24 : 22:03:05

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

Remarks :
1. PP from TblTxn_Value = KO, LO, MI, HE from TblRate_Percentage
2. XX from TblTxn_Value = XX only from TblRate_Percentage

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

if
TblTxn_ValueItemID = TblRate_PercentageID
and
SubID (PP), (XX, TT) = SubID (KO, LO, MI, HE, .....),(XX, TT)

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

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-24 : 22:55:47

[code]
UPDATE t
SET 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
AND (
t.SubID = 'PP' AND r.SubID IN ('KO', 'LO', 'MI', 'HE')
OR t.SubID = 'XX' AND r.SubID IN ('XX')
)

[/code]

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

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-06-24 : 23:10:26

quote:

Originally posted by khtan


UPDATE 	t
SET	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
       					   AND	(
       					   		t.SubID = 'PP' AND r.SubID IN ('KO', 'LO', 'MI', 'HE')
       					   	OR 	t.SubID = 'XX' AND r.SubID IN ('XX')
       					   	)

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

Thanks khTan. This what i want. i will put in the select statement instead on hardcode 'KO', 'LO', 'MI', 'HE'.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-24 : 23:22:49

quote:
'KO', 'LO', 'MI'

You must be hungry for some noodle. By any chance you are from Kuching ?

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

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-06-25 : 02:08:19

quote:
Originally posted by khtan

quote:
'KO', 'LO', 'MI'

You must be hungry for some noodle. By any chance you are from Kuching ?

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

, Hahahahaha, you are creative. I never noted this KO LO MI, hahhaahh. I just simply put few example. Really funny.

Anyway, thanks KHTan. But i am not from kuching!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources