CODEWITHSUNDEEP
sqlunion
E. Peterson
E. Peterson

Reputation: 452

SQL Union For Existing Query

I have the following query to display sales during a certain period of time, but unfortunately it seems like it is not reporting customers who have purchased in the other categories as well.

For example, the customer had never purchased products before (cat 2 or cat 3), and purchased products on 5/5 for the first time. That sale is not coming up on the report because they purchased something from cat 60 previously.

select CUSTOMER_DEL_HIST.CUSTOMER_NUMBER
      ,max ( CUSTOMER_DEL_HIST.SERVE_LOCATION) as SERVE_LOCATION
      ,max ( CUSTOMER_DEL_HIST.PRODUCT_NUMBER) as PRODUCT_NUMBER
      ,max ( CUSTOMER_DEL_HIST.DEL_DATE) as DEL_DATE
      ,max ( ORDER_RTE_BAL.WAREHOUSE) as WAREHOUSE
      ,max ( ORDER_RTE_BAL.ROUTE_NUMBER) as ROUTE_NUMBER
      ,max ( ORDER_RTE_BAL.ROUTE_DAY) as ROUTE_DAY
from CUSTOMER_DEL_HIST
        inner join INV_MASTER on CUSTOMER_DEL_HIST.PRODUCT_NUMBER = INV_MASTER.PRODUCT_NUMBER
        inner join ORDER_HEADER on CUSTOMER_DEL_HIST.ORDER_NUMBER = ORDER_HEADER.ORDER_NUMBER
        inner join ORDER_RTE_BAL
            on   ORDER_HEADER.WAREHOUSE_NUMBER = ORDER_RTE_BAL.WAREHOUSE
                and ORDER_HEADER.ROUTE_NUMBER = ORDER_RTE_BAL.ROUTE_NUMBER
                and ORDER_HEADER.ROUTE_DAY = ORDER_RTE_BAL.ROUTE_DAY
                and ORDER_HEADER.DELIVERY_DATE = ORDER_RTE_BAL.ROUTE_DATE
where    (CUSTOMER_DEL_HIST.DEL_DATE between X and Y)
        and (INV_MASTER.INVENTORY_CATEGORY in ('02', '03', '60', '74'))
        and (CUSTOMER_DEL_HIST.CUSTOMER_NUMBER not in 
            (select H2.CUSTOMER_NUMBER
            from CUSTOMER_DEL_HIST H2
            inner join INV_MASTER as I on H2.PRODUCT_NUMBER = I.PRODUCT_NUMBER
            where H2.DEL_DATE between '6/01/2014'   and '04/30/2015'
                and I.INVENTORY_CATEGORY in ('02', '03', '60', '74')))
group by CUSTOMER_DEL_HIST.CUSTOMER_NUMBER

I'm new to SQL but know that I should use a union in the SELECT, but that's about it. Unfortunately I'm not well versed enough, so I am looking for some help as to how I can go about getting the results I want.

Upvotes: 0

Views: 77

Answers (2)

TPhe
TPhe

Reputation: 1671

In the where clause of your query, the last piece has 

cdh.customer_number NOT IN 
    (
        SELECT h2.customer_number
        FROM customer_del_hist AS h2
        INNER JOIN inv_master AS i ON h2.product_number = i.product_number
        WHERE h2.del_date BETWEEN '6/01/2014' AND '04/30/2015'
            AND i.inventory_category IN ('02', '03', '60', '74') 
    )

This would be the area that is excluding customers. You can see that the subquery has a where clause, and that this where clause specifies that the inventory_category must be one of 02, 03, 60, 74. So you are finding all of the customers who purchased anything in any one of these categories, then excluding them from the results of your main query.

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

Can't offer much in the way of helping with the real problem but since you said you are new I thought I would show you how much cleaner your query can be with just a tiny bit of effort. If you start using aliases you can eliminate dozens of characters per line and your code can be a lot cleaner. A lot of formatting is personal preference but the code you posted is just way to challenging to read. Here is one way to utilize aliases and clean this up considerably.

SELECT cdh.customer_number
    , Max(cdh.serve_location) AS serve_location
    , Max(cdh.product_number) AS product_number
    , Max(cdh.del_date) AS del_date
    , Max(orb.warehouse) AS warehouse
    , Max(orb.route_number) AS route_number
    , Max(orb.route_day) AS route_day
FROM customer_del_hist cdh
INNER JOIN inv_master im ON cdh.product_number = im.product_number
INNER JOIN order_header oh ON cdh.order_number = oh.order_number
INNER JOIN order_rte_bal orb ON oh.warehouse_number = orb.warehouse
      AND oh.route_number = orb.route_number
      AND oh.route_day = orb.route_day
      AND oh.delivery_date = orb.route_date
WHERE cdh.del_date BETWEEN @StartDate AND @EndDate
    AND im.inventory_category IN ('02', '03', '60', '74')
    AND cdh.customer_number NOT IN 
    (
        SELECT h2.customer_number
        FROM customer_del_hist AS h2
        INNER JOIN inv_master AS i ON h2.product_number = i.product_number
        WHERE h2.del_date BETWEEN '6/01/2014' AND '04/30/2015'
            AND i.inventory_category IN ('02', '03', '60', '74') 
    ) 
GROUP  BY cdh.customer_number

Upvotes: 1

Related Questions