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]

Go to Top of Page

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
Go to Top of Page

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]

Go to Top of Page

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!
Go to Top of Page
   

- Advertisement -