SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculate commission and update set table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

calvinkwoo3000
Yak Posting Veteran

Singapore
97 Posts

Posted - 06/24/2009 :  22:03:05  Show Profile  Reply with Quote
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.

Edited by - calvinkwoo3000 on 06/24/2009 22:06:01

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 06/24/2009 :  22:55:47  Show Profile  Reply with Quote

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
Time is always against us

Go to Top of Page

calvinkwoo3000
Yak Posting Veteran

Singapore
97 Posts

Posted - 06/24/2009 :  23:10:26  Show Profile  Reply with Quote
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
Time is always against us





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)

Singapore
17635 Posts

Posted - 06/24/2009 :  23:22:49  Show Profile  Reply with Quote
quote:
'KO', 'LO', 'MI'

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


KH
Time is always against us

Go to Top of Page

calvinkwoo3000
Yak Posting Veteran

Singapore
97 Posts

Posted - 06/25/2009 :  02:08:19  Show Profile  Reply with Quote
quote:
Originally posted by khtan

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

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


KH
Time is always against us





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

Edited by - calvinkwoo3000 on 06/25/2009 02:52:57
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000