Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Query for picking correct address based on item number criteria

    I am struggling to build a single query to accomplish my goal. I could easily write a script but because I'm embedding this in an Excel data connection, it needs to be a single query.

    Basically, I need to pick the correct shipping address for each order based on what items are in the order, but also return the total revenue.

    Criteria
    Only COM items on order - use BillToCustKey
    Only non-COM - use ShipToCustKey
    Hybrid (both COM and non-COM) - use the non-COM items' ShipToCustKey

    My initial thinking was to get a distinct list of ShipToCustKeys where ItemNo <> 'COM', then union to another list where ItemNo = 'COM' and not exists(the first list). But this loses the revenue for the suppressed items. Another thought I had was taking a count(distinct ItemNo) by order where itemno = 'COM' and comparing that to the total count(distinct ItemNo) for the entire order, knowing that if they are equal, the order has COM only so use the BillToCustKey otherwise use the ShipToCustKey. But I couldn't get this working properly. Help?

    Here is a sample table that contains 3 orders, 1 for each of the possible criteria.

    Code:
    create table #Orders(OrdNo int, BillToCustNo int, ShipToCustNo int,ItemNo varchar(10),Revenue int)
    INSERT INTO #Orders values
    	(1,2,1,'COM',25),(1,2,2,'sgjhs',15)
    	,(2,3,3,'wersfd',10),(2,3,3,'sdtfsd',45)
    	,(3,5,1,'COM',25)
    Evaluation
    - OrdNo 1 has both COM and non-COM so attribute all revenue to the ShipToCustKey of the non-COM item.
    - OrdNo 2 only has non-COM items, so attribute all revenue to the ShipToCustKey
    - OrdNo 3 has only COM items, so attribute all revenue to the BillToCustKey


    Desired Output
    Just need the output by the "fixed" address
    Click image for larger version. 

Name:	KvXPUa1.png 
Views:	1 
Size:	1.6 KB 
ID:	16803
    Last edited by clawlan; 03-09-16 at 14:13.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What happens when a single OrdNo has multiple values for BillToCustNo and/or ShipToCustNo? This is definitely possible, but not included in your sample data.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    What happens when a single OrdNo has multiple values for BillToCustNo and/or ShipToCustNo? This is definitely possible, but not included in your sample data.

    -PatP
    Good question. Ideally, non-COM items would always use the ShipToCustKey they are associated with, and the COM items will use the max() shipto/billto depending on which criteria the order falls into.

    The purpose of this query is to allow analysis of revenue by state. But the issue is the COM items ship to a special vendor first before going to the customer which causes the ShipTo to be this vendor as opposed to the actual customer, so this rough logic is being used to get around that. This is not being used for finance or forecasting, just rough directional analysis, so it doesn't need to be absolute.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try this as a first attempt, it may need some tuning:
    Code:
    DROP TABLE #Orders
    GO
    
    CREATE TABLE #Orders (
       OrdNo        int
    ,  BillToCustNo int
    ,  ShipToCustNo int
    ,  ItemNo       varchar(10)
    ,  Revenue      int
    )
    
    INSERT INTO #Orders
       VALUES
          (1, 2, 1, 'COM',    25)
    ,     (1, 2, 2, 'sgjhs',  15)
    ,     (2, 3, 3, 'wersfd', 10)
    ,     (2, 3, 3, 'sdtfsd', 45)
    ,     (3, 5, 1, 'COM'   , 25)
    
    ; WITH cte AS (
    SELECT OrdNo, BillToCustNo, ShipToCustNo
    ,  Sum(CASE WHEN 'COM' = ItemNo THEN 1 ELSE 0 END) AS Count_COM
    ,  Sum(CASE WHEN 'COM' = ItemNo THEN 0 ELSE 1 END) AS Count_Non
    ,  Sum(Revenue) AS TotRevenue
       FROM #Orders
       GROUP BY OrdNo, BillToCustNo, ShipToCustNo
    )
    SELECT CASE WHEN 0 = Count_Non THEN BillToCustNo ELSE ShipToCustNo END AS CustNo
    ,  TotRevenue
       FROM cte
       ORDER BY OrdNo, BillToCustNo, ShipToCustNo
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Thanks Pat, let me play with it a bit...

Posting Permissions

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