# Thread: Query for picking correct address based on item number criteria

1. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
211

## 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
Last edited by clawlan; 03-09-16 at 13:13.

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

3. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
211
Originally Posted by Pat Phelan
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

5. Registered User
Join Date
Oct 2010
Location
Atlanta, GA
Posts
211