locked
Select Query Help RRS feed

  • Question

  • User-1551254158 posted

    Scenario: I have two tables, first table consists of Sales Transaction (tblTransactions), the second table (tblPercentages) contains a list of Employee IDs, customer IDs and percentages . The second table lists unique employee IDs and their standard percentage rate (customer id = STDRATE). However, there a exceptions where an employee will get a different rate per specific customer.  

    I am unsure on how to select the standard prercentage rate of those with a CustomerID = STDRATE, when the customerID of a transaction does not exist in tblPercentages. Any suggestions would be greatly appreciated.

    Sample table: tblPercentages

    EmployeeID CustomerID Percentage
    BNCINSUR STDRATE 20
    BRADLEYP STDRATE 25
    BWDGROUP STDRATE 30
    BWDGROUP NASSAU2 28

    Sample table: tblTransactions 

    CustomerID EmployeeID Amount
    ALLFAM BNCINSUR 50
    NYCCO BRADLEYP 55
    NASSAU2 BWDGROUP 37
    ABCCO BWDGROUP 42

     Basically, I would like this result:

    CustomerID EmployeeID Amount Percentage
    ALLFAM BNCINSUR 50 20
    NYCCO BRADLEYP 55 25
    NASSAU2 BWDGROUP 37 30
    ABCCO BWDGROUP 42 28

     

    Tuesday, July 10, 2012 3:10 PM

Answers

  • User-1199946673 posted
    SELECT     T.CustomerID 
              ,T.EmployeeID 
              ,T.Amount 
              ,IIF(P1.Percentage IS NULL, P2.Percentage, P1.Percentage) as Percentage  
    FROM       (tblTransactions T  
    LEFT JOIN  (SELECT EmployeeID
                      ,Percentage
                FROM   tblPercentages
                WHERE  CustomerID <> 'STDRATE') P1 ON T.EmployeeID = P1.EmployeeID) 
    LEFT JOIN  (SELECT EmployeeID
                      ,Percentage
                FROM   tblPercentages
                WHERE  CustomerID = 'STDRATE') P2 ON T.EmployeeID = P2.EmployeeID
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 12, 2012 9:41 AM

All replies

  • User306743125 posted

    Try this....

    SELECT T.CustomerID, T.EmployeeID, T.Amount, 
    (CASE WHEN P2.Percentage  = NULL THEN P1.Percentage ELSE P2.Percentage END)as Percentage
    FROM tblTransactions T
    LEFT JOIN tblPercentages P1
    ON T.EmployeeID = P1.EmployeeID 
    LEFT JOIN tblPercentages P2
    ON T.EmployeeID = P2.EmployeeID AND T.CustomerID = P2.CustomerID
    

    Tuesday, July 10, 2012 3:52 PM
  • User-1551254158 posted

    Basquiat,

    Thank you for the quick reply. However, there is a syntax error (missing operator) in query expression '(CASE WHEN P2.Percentage  = NULL THEN P1.Percentage ELSE P2.Percentage END)'.

    Please advise.

    Tuesday, July 10, 2012 4:28 PM
  • User306743125 posted

    I don't see where it is missing an operator....but try replacing the 'P2.Percentage = NULL' with 'P2.Percentage IS NULL'

    Wednesday, July 11, 2012 1:31 AM
  • User-1199946673 posted

    Try this....

    SELECT T.CustomerID, T.EmployeeID, T.Amount,  (CASE WHEN P2.Percentage  = NULL THEN P1.Percentage ELSE P2.Percentage END)as Percentage  FROM tblTransactions T  LEFT JOIN tblPercentages P1  ON T.EmployeeID = P1.EmployeeID  LEFT JOIN tblPercentages P2  ON T.EmployeeID = P2.EmployeeID AND T.CustomerID = P2.CustomerID

    However, there is a syntax error (missing operator) in query expression '(CASE WHEN P2.Percentage = NULL THEN P1.Percentage ELSE P2.Percentage END)'.

    This thread is posted in the Access Databases and AccessDataSource Control Forum, so most likely he's using Access., so this will throw a syntax error.

    I don't see where it is missing an operator....but try replacing the 'P2.Percentage = NULL' with 'P2.Percentage IS NULL'

    x = NULL will never be true, even if x has a value of NULL. however this will not cause a syntax error, the resut will always be false

    SELECT     T.CustomerID
              ,T.EmployeeID
              ,T.Amount
              ,IIF(P1.Percentage IS NULL, P2.Percentage, P1.Percentage) as Percentage 
    FROM       (tblTransactions T 
    LEFT JOIN  tblPercentages  P1 ON T.EmployeeID = P1.EmployeeID)
    LEFT JOIN  tblPercentages  P2 ON T.EmployeeID = P2.EmployeeID  AND T.CustomerID = 'STDRATE'
    

    Wednesday, July 11, 2012 4:06 AM
  • User-1551254158 posted

    hans_v,

    Start of by saying thank you for responding. The result of your query returned 'JOIN expression not supported. I changed your query up by adding the parenthesis, but resulted with no data returned. Any suggestions?

    Below is the query I ran:

    SELECT     T.CustomerID ,
                     T.EmployeeID ,
                     T.Amount,
                     IIF(P1.Percentage IS NULL, P2.Percentage, P1.Percentage) as Percentage
    FROM       (tblTransactions as T  
    LEFT JOIN  tblPercentages as  P1 ON T.EmployeeID = P1.EmployeeID)
    LEFT JOIN  tblPercentages  as P2 ON ((T.EmployeeID = P2.EmployeeID ) AND (T.CustomerID = 'STDRATE'))

    Wednesday, July 11, 2012 2:00 PM
  • User3866881 posted

    EmployeeID CustomerID Percentage
    BNCINSUR STDRATE 20
    BRADLEYP STDRATE 25
    BWDGROUP STDRATE 30
    BWDGROUP NASSAU2 28

    Sample table: tblTransactions 

    CustomerID EmployeeID Amount
    ALLFAM BNCINSUR 50
    NYCCO BRADLEYP 55
    NASSAU2 BWDGROUP 37
    ABCCO BWDGROUP 42

    Hi,

    I feel very strange and interesting——Why CustomerID is quite different from that defined in your 2nd table?Why do you design like this?Maybe tell us more about your design and we can change them to make this simplified to be solved……

    Reguards

    Wednesday, July 11, 2012 10:01 PM
  • User-1199946673 posted
    SELECT     T.CustomerID 
              ,T.EmployeeID 
              ,T.Amount 
              ,IIF(P1.Percentage IS NULL, P2.Percentage, P1.Percentage) as Percentage  
    FROM       (tblTransactions T  
    LEFT JOIN  (SELECT EmployeeID
                      ,Percentage
                FROM   tblPercentages
                WHERE  CustomerID <> 'STDRATE') P1 ON T.EmployeeID = P1.EmployeeID) 
    LEFT JOIN  (SELECT EmployeeID
                      ,Percentage
                FROM   tblPercentages
                WHERE  CustomerID = 'STDRATE') P2 ON T.EmployeeID = P2.EmployeeID
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 12, 2012 9:41 AM