how to inside 2 action in the select CASE? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2008)

how to inside 2 action in the select CASE?

Author

Topic

anthor
Starting Member

14 Posts

Posted - 2012-08-31 : 09:59:20

AccNo Commision Add_By_AccNo
2 6.000 0
3 4.000 2
5 3.000 3

WITH Dependencies AS(SELECT * FROM Tbl_Account WHERE AccNo = 5 UNION ALL SELECT t.* FROM Tbl_Account t JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo)
	INSERT INTO Tbl_Transaction(Before_Amount,After_Amount,Amount,Transaction_AccNo,Remark,Transaction_Date,TransType_ID,Comm)
	SELECT Balance,Balance+((SELECT Topup_Amount FROM Tbl_Topup WHERE Topup_ID=8)*Commision/100),((SELECT Topup_Amount FROM Tbl_Topup WHERE Topup_ID=8)*Commision/100),AccNo,'',GETDATE(),2,(SELECT CASE WHEN Dependencies.Add_By_AccNo > 0 THEN (SELECT DISTINCT(Commision) FROM Tbl_Account WHERE AccNo=Dependencies.Add_By_AccNo)-Dependencies.Commision ELSE Dependencies.Commision end as Comm FROm Tbl_Account WHERE AccNo = 5) FROM Dependencies

(SELECT CASE WHEN Dependencies.Add_By_AccNo > 0 THEN (SELECT DISTINCT(Commision) FROM Tbl_Account WHERE AccNo=Dependencies.Add_By_AccNo)-Dependencies.Commision ELSE Dependencies.Commision end as Comm FROm Tbl_Account WHERE AccNo = 5) FROM Dependencies

AccNo 5 is create By AccNo 3
AccNo 3 is create By AccNo 2
AccNo 2 is create By AccNo 0 //no body

1)so when AccNo 5 puchase items,AccNo 5 will Get the Commision 3%
2)and AccNo 3 will get 1 % because AccNo 3 is given Commision to AccNo 5 for 3% already, so 4% minus 3 %,Acc No 3 will get 1 % Commision only
3)AccNo 2 is Leader,so,AccNo 2 no more bosses,and his 6% commision ,it is given for Acc No 3 4% already,so need to minus 6%-4%,AccNo 2 is only get 2 % commision only

when sql query read until last User ID:2,and By_By_AccNo is 0, dont have this ID,mean the related data are finish,and i want to minus AccNo 3 Commision mean, 6.000 Commision- 4.000 Commision

how to do this?

p/s:sql will automatically read from AccNo 5 and automatically calculate the Commision should pay them,but until Top Acc Commision there is a problem,because my query is read from lowest pryamid chart until top,but until top need to read the down line commision and minus it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 10:14:55

whatever you've now doesnt make much sense. whats the relevance of select inbetween? you cant mix retrieval with assignment in same select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 10:32:07

sounds like what you need is recursive cte . have a look at below link

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anthor
Starting Member

14 Posts

Posted - 2012-08-31 : 10:39:17

No,i dont think so.
im using CTE read the most lowest member until top,
but until top,need to read back the second top Commision,this is the problem

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 11:24:01

quote:
Originally posted by anthor

No,i dont think so.
im using CTE read the most lowest member until top,
but until top,need to read back the second top Commision,this is the problem

hmm?
its still recursive logic isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anthor
Starting Member

14 Posts

Posted - 2012-08-31 : 11:35:02

Tbl_Account
AccNo Commision Add_By_AccNo Level
2 6.000 0 MANAGER
3 4.000 2 SUPERVISOR
5 3.000 3 SALEPERSON

Tbl_Transaction //this is the example insert query success
AccNo Commision Amount
5 1% $0.10
3 2% $0.20
2 2% $0.20

This is the Database with pyramid sale Commision.
AccNo 5 is the most lowest level is the SALE PERSON,
1)When AccNo 5 purchase a item is $10.00 AccNo 5 will Get 3% Commision,
2)and his SUPERVISOR get will 1%,because total of SUPERVISOR Commision is only 4%,but SUPERVISOR need to give SALEPERSON 3% Commision
3)and MANAGER LEVEL will get 2% commision of $10.00 ,is because MANAGER need to give SUPERVISOR 4% commision

Here is my sample code with CTE,one SQL query inside all Commision into Tbl_Transaction(will be inserted 3 records.)
but unfortunately MANAGER LEVEL commision can't work....

WITH Dependencies AS(SELECT * FROM Tbl_Account WHERE AccNo = 5 UNION ALL SELECT t.* FROM Tbl_Account t JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo)
	INSERT INTO Tbl_Transaction(Before_Amount,After_Amount,Amount,Transaction_AccNo,Remark,Transaction_Date,TransType_ID,Comm)
	SELECT Balance,Balance+((SELECT Topup_Amount FROM Tbl_Topup WHERE Topup_ID=8)*Commision/100),((SELECT Topup_Amount FROM Tbl_Topup WHERE Topup_ID=8)*Commision/100),AccNo,'',GETDATE(),2,(SELECT CASE WHEN Dependencies.Add_By_AccNo > 0 THEN (SELECT DISTINCT(Commision) FROM Tbl_Account WHERE AccNo=Dependencies.Add_By_AccNo)-Dependencies.Commision ELSE Dependencies.Commision end as Comm FROm Tbl_Account WHERE AccNo = 5) FROM Dependencies

anthor
Starting Member

14 Posts

Posted - 2012-08-31 : 11:36:21

how to in select query save the last row Commision Value?have any idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 11:44:22

so is the attempt to populate transaction table? how are transaction value inputted?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anthor
Starting Member

14 Posts

Posted - 2012-08-31 : 13:10:44

quote:
Originally posted by visakh16

so is the attempt to populate transaction table? how are transaction value inputted?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DECLARE @Tbl_Account	TABLE(
	AccNo	int,
	Add_By_AccNo	int,
	Comm	decimal(18,8))
	INSERT @Tbl_Account VALUES(2,0,6),(3,2,4),(5,3,3),(6,2,4)

	DECLARE @Tbl_Transaction TABLE(
	Transaction_AccNo	int,
	Comm	decimal(18,8),
	Amount	decimal(18,8))

	WITH Dependencies AS(
	SELECT * FROM @Tbl_Account WHERE AccNo = 5 UNION ALL 
	SELECT t.* FROM @Tbl_Account t 
	JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo)
	INSERT INTO @Tbl_Transaction(Amount,Comm,Transaction_AccNo)
	SELECT 10,d2.Comm,d1.AccNo FROM Dependencies d1 LEFT JOIN Dependencies d2 ON d1.Add_By_AccNo = d2.AccNo

this sql will test with $10.00 purchase price.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources