August 31, 2012 at 7:57 am
declare @LastAccNo int
SELECT Comm =
CASE
WHEN (Add_By_AccNo > 0) THEN
(SELECT Digi_Comm FROM Tbl_Account WHERE AccNo=3)-Digi_Comm
Set @LastAccNo = AccNo
ELSE
(SELECT Digi_Comm FROM Tbl_Account WHERE AccNo=3)-Digi_Comm
end
FROm Tbl_Account WHERE AccNo = 2
how to inside 2 action in the select CASE?
August 31, 2012 at 8:12 am
You can't put a SET inside a SELECT.
You should separate your statements.
Another point is that both statements in your CASE are the same.
What are you trying to accomplish?
--Modification set as an example, not recommended for real use.
declare @LastAccNo int
SELECT Comm =
CASE
WHEN (Add_By_AccNo > 0) THEN
(SELECT Digi_Comm FROM Tbl_Account WHERE AccNo=3)-Digi_Comm
ELSE
(SELECT Digi_Comm FROM Tbl_Account WHERE AccNo=3)-Digi_Comm
end
FROM Tbl_Account WHERE AccNo = 2
Set @LastAccNo = AccNo
FROM Tbl_Account WHERE AccNo = 2
WHERE (Add_By_AccNo > 0)
August 31, 2012 at 8:27 am
AccNo Commision Add_By_AccNo
2 6.000 0
3 4.0002
5 3.0003
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.
August 31, 2012 at 8:40 am
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
August 31, 2012 at 9:08 am
chinye2020 (8/31/2012)
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
Your above explanation is totally unclear. Before anybody really has much chance helping you, you need to provide some details to work with. ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. Also, you need to present the ENTIRE problem up front not piece meal.
Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2012 at 9:34 am
Tbl_Account
AccNo Commision Add_By_AccNo Level
2 6.000 0 MANAGER
3 4.0002 SUPERVISOR
5 3.0003 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
August 31, 2012 at 9:36 am
how to in select query save the last row Commision Value?have any idea?
August 31, 2012 at 9:40 am
Sean is right, DDL and sample data would have been a great help. As well as posting your whole problem.
You should pay more attention to your code to make it readable (not just 3 lines).
You shouldn't be doing all those subqueries, they will kill the performance on larger datasets. Each subquery will execute once per row.
If SELECT Topup_Amount FROM Tbl_Topup WHERE Topup_ID=8
is not dependant on your actual query, then you should store the value in a variable or you should add it on another CTE.
I'll leave you an example of the code, but try to understand what is doing on each step.
DECLARE @Tbl_AccountTABLE(
Accnoint,
Add_By_AccNoint,
Digi_Commdecimal(18,8),
Balancedecimal(18,8))
INSERT @Tbl_Account VALUES(2,0,6,100),(3,2,4,50),(5,3,3,25),(4,2,4,50)
WITH Dependencies AS(
SELECTAccno,
Add_By_AccNo,
Digi_Comm,
Balance
FROM @Tbl_Account
WHERE AccNo = 5
UNION ALL
SELECTt.Accno,
t.Add_By_AccNo,
t.Digi_Comm,
t.Balance
FROM @Tbl_Account t
JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo
),
OtherCTE AS (
SELECT 100/*Topup_Amount FROM Tbl_Topup WHERE Topup_ID=8*/ TopupAmount
)
--INSERT INTO Tbl_Transaction(
--Before_Amount,
--After_Amount,
--Amount,
--Transaction_AccNo,
--Remark,
--Transaction_Date,
--TransType_ID,
--Comm)
SELECTd1.Balance,
d1.Balance +(
TopupAmount
*(d1.Digi_Comm-ISNULL(d2.Digi_Comm, 0))
/100
),
TopupAmount
*(d1.Digi_Comm-ISNULL(d2.Digi_Comm, 0))
/100,
d1.Accno,
'',
GETDATE(),
2,
d1.Digi_Comm-ISNULL(d2.Digi_Comm, 0)
FROM Dependencies d1
LEFT
JOIN Dependencies d2 ON d2.Add_By_AccNo = d1.Accno
CROSS
JOIN OtherCTE
August 31, 2012 at 9:41 am
You really need to read the article I suggested. You need to provide ddl (create table scripts) and sample data as inserts. We are all volunteers around here and I really don't have time to try to piece meal this into something I can work with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2012 at 9:42 am
chinye2020 (8/31/2012)
how to in select query save the last row Commision Value?have any idea?
I am not at all sure what that means??? Keep in mind that tables are unordered set of data, there is no concept of first, last, next or whatever.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2012 at 10:05 am
Luis Cazares (8/31/2012)
Sean is right, DDL and sample data would have been a great help. As well as posting your whole problem.You should pay more attention to your code to make it readable (not just 3 lines).
You shouldn't be doing all those subqueries, they will kill the performance on larger datasets. Each subquery will execute once per row.
If SELECT Topup_Amount FROM Tbl_Topup WHERE Topup_ID=8
is not dependant on your actual query, then you should store the value in a variable or you should add it on another CTE.
I'll leave you an example of the code, but try to understand what is doing on each step.
DECLARE @Tbl_AccountTABLE(
Accnoint,
Add_By_AccNoint,
Digi_Commdecimal(18,8),
Balancedecimal(18,8))
INSERT @Tbl_Account VALUES(2,0,6,100),(3,2,4,50),(5,3,3,25),(4,2,4,50)
WITH Dependencies AS(
SELECTAccno,
Add_By_AccNo,
Digi_Comm,
Balance
FROM @Tbl_Account
WHERE AccNo = 5
UNION ALL
SELECTt.Accno,
t.Add_By_AccNo,
t.Digi_Comm,
t.Balance
FROM @Tbl_Account t
JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo
),
OtherCTE AS (
SELECT 100/*Topup_Amount FROM Tbl_Topup WHERE Topup_ID=8*/ TopupAmount
)
--INSERT INTO Tbl_Transaction(
--Before_Amount,
--After_Amount,
--Amount,
--Transaction_AccNo,
--Remark,
--Transaction_Date,
--TransType_ID,
--Comm)
SELECTd1.Balance,
d1.Balance +(
TopupAmount
*(d1.Digi_Comm-ISNULL(d2.Digi_Comm, 0))
/100
),
TopupAmount
*(d1.Digi_Comm-ISNULL(d2.Digi_Comm, 0))
/100,
d1.Accno,
'',
GETDATE(),
2,
d1.Digi_Comm-ISNULL(d2.Digi_Comm, 0)
FROM Dependencies d1
LEFT
JOIN Dependencies d2 ON d2.Add_By_AccNo = d1.Accno
CROSS
JOIN OtherCTE
Luis,your code almost let me done,but why this sql query can't work?
WITH Dependencies AS(
SELECT * FROM Tbl_Account WHERE AccNo = 5 UNION ALL
SELECT * FROM Tbl_Account t
JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo)
SELECT * FROM Dependencies d1 LEFT JOIN Dependencies d2 ON d2.Add_By_AccNo = d1.Accno
August 31, 2012 at 10:13 am
chinye2020 (8/31/2012)
Luis Cazares (8/31/2012)
Sean is right, DDL and sample data would have been a great help. As well as posting your whole problem.You should pay more attention to your code to make it readable (not just 3 lines).
You shouldn't be doing all those subqueries, they will kill the performance on larger datasets. Each subquery will execute once per row.
If SELECT Topup_Amount FROM Tbl_Topup WHERE Topup_ID=8
is not dependant on your actual query, then you should store the value in a variable or you should add it on another CTE.
I'll leave you an example of the code, but try to understand what is doing on each step.
DECLARE @Tbl_AccountTABLE(
Accnoint,
Add_By_AccNoint,
Digi_Commdecimal(18,8),
Balancedecimal(18,8))
INSERT @Tbl_Account VALUES(2,0,6,100),(3,2,4,50),(5,3,3,25),(4,2,4,50)
WITH Dependencies AS(
SELECTAccno,
Add_By_AccNo,
Digi_Comm,
Balance
FROM @Tbl_Account
WHERE AccNo = 5
UNION ALL
SELECTt.Accno,
t.Add_By_AccNo,
t.Digi_Comm,
t.Balance
FROM @Tbl_Account t
JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo
),
OtherCTE AS (
SELECT 100/*Topup_Amount FROM Tbl_Topup WHERE Topup_ID=8*/ TopupAmount
)
--INSERT INTO Tbl_Transaction(
--Before_Amount,
--After_Amount,
--Amount,
--Transaction_AccNo,
--Remark,
--Transaction_Date,
--TransType_ID,
--Comm)
SELECTd1.Balance,
d1.Balance +(
TopupAmount
*(d1.Digi_Comm-ISNULL(d2.Digi_Comm, 0))
/100
),
TopupAmount
*(d1.Digi_Comm-ISNULL(d2.Digi_Comm, 0))
/100,
d1.Accno,
'',
GETDATE(),
2,
d1.Digi_Comm-ISNULL(d2.Digi_Comm, 0)
FROM Dependencies d1
LEFT
JOIN Dependencies d2 ON d2.Add_By_AccNo = d1.Accno
CROSS
JOIN OtherCTE
Luis,your code almost let me done,but why this sql query can't work?
WITH Dependencies AS(SELECT * FROM Tbl_Account WHERE AccNo = 5 UNION ALL
SELECT * FROM Tbl_Account t
JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo)
SELECT * FROM Dependencies d1 LEFT JOIN Dependencies d2 ON d2.Add_By_AccNo = d1.Accno
What does the error message say?
Msg 205, Level 16, State 1, Line 8
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Look at your CTE. You select all columns from your table and try to union that back to itself. This means you will now have doubled the columns. You need to have the same number of columns in both sides of a UNION. You either need t.* or d.* depending on what you are trying to do with your rCTE.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2012 at 10:53 am
Tbl_Account
AccNo Comm 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
Transaction_AccNo Comm Amount
5 1% $0.10
3 2% $0.20
2 2% $0.20 //how to insert this,problem at here
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.
August 31, 2012 at 10:55 am
chinye2020 (8/31/2012)
Tbl_AccountAccNo Comm 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
Transaction_AccNo Comm Amount
5 1% $0.10
3 2% $0.20
2 2% $0.20 //how to insert this,problem at here
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.
I give up...I have asked for something to work with and you just keep posting vague descriptions and there is still no ddl or sample data. Maybe if you post the basics of giving somebody a chance at helping you will find somebody with more patience than I have today. Good luck.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2012 at 11:02 am
Sean Lange (8/31/2012)
chinye2020 (8/31/2012)
Tbl_AccountAccNo Comm 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
Transaction_AccNo Comm Amount
5 1% $0.10
3 2% $0.20
2 2% $0.20 //how to insert this,problem at here
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.
I give up...I have asked for something to work with and you just keep posting vague descriptions and there is still no ddl or sample data. Maybe if you post the basics of giving somebody a chance at helping you will find somebody with more patience than I have today. Good luck.
DECLARE @Tbl_AccountTABLE(
AccNoint,
Add_By_AccNoint,
Commdecimal(18,8))
INSERT @Tbl_Account VALUES(2,0,6),(3,2,4),(5,3,3),(6,2,4)
DECLARE @Tbl_Transaction TABLE(
Transaction_AccNoint,
Commdecimal(18,8),
Amountdecimal(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 is the sample data?
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply