James Wilson James Wilson - 1 month ago 6
SQL Question

Excluding data based on selling product type

I have a dataset returning me a list of sellers and how much on average they make per sale.

I need to filter this dataset so it only shows me sellers who are selling both product types. I've tried to group it, which makes me group on all select elements and nothing is returned.

Here is the query I am currently using.

SELECT [Agent] ,
Contract ,
QPivot.[1] AS Q1 ,
QPivot.[2] AS Q2 ,
QPivot.[3] AS Q3 ,
QPivot.[4] AS Q4
FROM ( SELECT c.sagent_number [Agent] ,
YEAR(c.dtcontract_entered) [Year] ,
CASE WHEN c.scontract_no LIKE '%zm%' THEN 'Product 2'
WHEN c.scontract_no LIKE '%dap%' THEN 'Product 1'
WHEN c.scontract_no LIKE '%v0%' THEN 'Product 1'
ELSE NULL
END [Contract] ,
DATEPART(QUARTER, c.dtcontract_entered) [Quarter] ,
AVG(c.cretail_rate - c.cnet_rate) [AVG Sales]
FROM scs_contracts c
WHERE c.iproduct_type_id = 4
AND c.sstatus IN ('P', 'A', 'C', 'E')
GROUP BY c.sagent_number ,
YEAR(c.dtcontract_entered) ,
CASE WHEN c.scontract_no LIKE '%zm%' THEN 'Product 2'
WHEN c.scontract_no LIKE '%dap%' THEN 'Product 1'
WHEN c.scontract_no LIKE '%v0%' THEN 'Product 1'
ELSE NULL
END ,
DATEPART(QUARTER, c.dtcontract_entered)
) AS QuarterlyData PIVOT( AVG([AVG Sales]) FOR Quarter IN ( [1], [2], [3], [4] ) ) AS QPivot
WHERE Contract IS NOT NULL
AND Year = 2016
AND [Agent] <> '2'
AND [Agent] <> '3'
ORDER BY [QPivot].[Agent], QPivot.Contract asc


Here is a sample of the data returned:

enter image description here

I am trying to filter out the sellers who have only sold a single product. All of the sellers who have sold product 1 and product 2 I'd like to remain in the data set. Is this possible?

The image below the ones highlighted in the color should not appear. I've tried wrapping it in a select from, I've tried grouping. I'm sure it has got to be possible somehow. I've tried to somehow do a count on [Agent], but am unable without grouping on every select column. I'm at a loss at this point.

enter image description here

Answer

You can put QPIVOT as a temp table and then check for existence of the other product like this:

...

 SELECT  [Agent] ,
            Contract ,
            Q1 ,
            Q2 ,
            Q3 ,
            Q4
    FROM #QPIVOT
    WHERE EXISTS 
        (
        SELECT 1 FROM #QPIVOT QP2 where QP2.AGENT=#QPIVOT.AGENT AND QP2.Contract<>#QPIVOT.Contract)

 ORDER BY [Agent],Contract asc
Comments