Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    15

    Unanswered: how to inside 2 action in the select CASE?

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

    Code:
    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.

    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

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try something like that:

    Code:
    with CTE as
    (
        select t.*, cast(0 as decimal(5, 2)) as CommisionLow
        from Tbl_Account as t
        where AccNo = 5
        
        union all
        
        select t.*, c.Commision
        from Tbl_Account as t
        join CTE as c on c.Add_By_AccNo = t.AccNo
    )
    
    select * from CTE
    Hope this helps.

  3. #3
    Join Date
    Aug 2012
    Posts
    15
    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,TransTyp e_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

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

  4. #4
    Join Date
    Aug 2012
    Posts
    15
    Quote Originally Posted by imex View Post
    Try something like that:

    Code:
    with CTE as
    (
        select t.*, cast(0 as decimal(5, 2)) as CommisionLow
        from Tbl_Account as t
        where AccNo = 5
        
        union all
        
        select t.*, c.Commision
        from Tbl_Account as t
        join CTE as c on c.Add_By_AccNo = t.AccNo
    )
    
    select * from CTE
    Hope this helps.
    this only select,i need INSERT

  5. #5
    Join Date
    Aug 2012
    Posts
    15
    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.

  6. #6
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    WITH Dependencies AS
    (
        SELECT AccNo, Add_By_AccNo, Comm, CAST(0 as decimal(18,8)) AS CommLow 
        FROM @Tbl_Account 
        WHERE AccNo = 5 
    
        UNION ALL 
    
        SELECT t.AccNo, t.Add_By_AccNo, t.Comm, d.Comm 
        FROM @Tbl_Account t 
        JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo
    )
    
    INSERT INTO @Tbl_Transaction 
        (Amount, Comm, Transaction_AccNo)
        SELECT 10, Comm - CommLow, AccNo FROM Dependencies
     
    select * from @Tbl_Transaction
    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •