how to inside 2 action in the select CASE?
-
chinye2020 (8/31/2012)
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?
Well you have posted what Luis created for you and then you are asking how to get your actual data correct.
Let's take the sample that Luis posted. I formatted this a little bit so it is easier to read.
DECLARE @Tbl_Account TABLE
(
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
select * from @Tbl_Transaction
So now we have a table filled with some data. I have no idea what the issue is at this point.
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
Can you explain what that is and how it relates to the table created above? It is very unclear what you are trying to do.
_______________________________________________________________
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/ -
Msg 319, Level 15, State 1, Line 13Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
what is this sql problem??why can't execute?
-
That's not complete.
Where is the balance column on @Tbl_Account?
Where's Tbl_Topup?
You should give us something that we can use by just copying, pasting and executing.
-
chinye2020 (8/31/2012)
Msg 319, Level 15, State 1, Line 13Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
what is this sql problem??why can't execute?
Read the error message. It tells you EXACTLY what the problem is.
_______________________________________________________________
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/ -
chinye2020 (8/31/2012)
Msg 319, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
what is this sql problem??why can't execute?
Read the error and try to fix it.
-
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
Sean,understand how the calculation to insert Tbl_Transaction???????this is the pyramid sale commision..
-
chinye2020 (8/31/2012)
Tbl_AccountAccNo 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
Sean,understand how the calculation to insert Tbl_Transaction???????this is the pyramid sale commision..
WOW even more rules this late in the game??? Where is the DDL? Where is the sample data? Have you actually read the article I have suggested repeatedly?
Your ddl should be something like this:
create table #MyTable
(
columns here
)
Repeat that for ALL the tables.
Then for sample data you need insert statements.
Read the article, post some ddl and sample data and you will get help. YOU have to put in some effort for this process to work.
_______________________________________________________________
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/ -
DECLARE @Tbl_AccountTABLE(AccNoint,
Add_By_AccNoint,
Commdecimal(18,8),
lvl nvarchar(50))
INSERT @Tbl_Account VALUES(2,0,6,'MANAGER'),(3,2,4,'AGENT'),(5,3,3,'SALE PERSON'),(6,2,4,'AGENT')
DECLARE @Tbl_Transaction TABLE(
Transaction_AccNoint,
Commdecimal(18,8),
Amountdecimal(18,8))
DECLARE @PurchasePrice decimal(18,3)
set @PurchasePrice = 10.00;
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 @PurchasePrice,d2.Comm-d1.Comm,d1.AccNo FROM Dependencies d1 LEFT JOIN Dependencies d2 ON d1.Add_By_AccNo = d2.AccNo
select * from @Tbl_Transaction
see the AccNo 2 result,is null,AccNo 2 Result should be take the AccNo 2's Commision rate is 6% and deduct the AccNo 3 4% Commision,reuslt need to display 2% for Acc No 2
-
What was wrong with my query?
I suggest you to read this post as well
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
-
-
dude,unserstand?
-
My code won't return that result at all.
Please check again. I'm posting the same code and it's giving me the expected results.
Take the time to understand the code and be suro to know what it is doing.
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),(6,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 10/*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
) After_Amount,
TopupAmount
*(d1.Digi_Comm-ISNULL(d2.Digi_Comm, 0))
/100 Amount,
d1.Accno Transaction_AccNo,
'' Remark,
GETDATE() Transaction_Date,
2 TransType_ID,
d1.Digi_Comm-ISNULL(d2.Digi_Comm, 0) Comm
FROM Dependencies d1
LEFT
JOIN Dependencies d2 ON d2.Add_By_AccNo = d1.Accno
CROSS
JOIN OtherCTE
-
problem solved,thanks
DECLARE @Tbl_AccountTABLE(AccNoint,
Add_By_AccNoint,
Commdecimal(18,8),
lvl nvarchar(50))
INSERT @Tbl_Account VALUES(2,0,6,'MANAGER'),(3,2,4,'SUPERVISOR'),(5,3,3,'SALE PERSON'),(6,2,4,'SUPERVISOR')
DECLARE @Tbl_Transaction TABLE(
Transaction_AccNoint,
Commdecimal(18,8),
Amountdecimal(18,8))
DECLARE @PurchasePrice decimal(18,3)
set @PurchasePrice = 10.00;
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 @PurchasePrice,d1.Comm-ISNULL((SELECT Comm FROM Dependencies WHERE Add_By_AccNo=d1.AccNo),0),d1.AccNo FROM Dependencies d1 LEFT JOIN Dependencies d2 ON d1.Add_By_AccNo = d2.AccNo
select * from @Tbl_Transaction
-
I'm almost sure that you're not completely sure on what the code is doing.
You added a completely unnecesary subquery that will increase the work on the server.
After you added the subquery, you keeped the JOIN (which was meant to be there to avoid the subquery) and you say that my query won't work after you changed the fields on the join.
Be very careful, if you don't fully understand how a code is working, you shouldn't use it.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply