Report on customers with NO sales for specific product line

Try this:

SELECT *
FROM  Customer LEFT JOIN SalesHistory ON Customer.CustID = SalesHistory.CustID
WHERE SalesHistory.CustID IS NULL AND {add criteria for product line here if needed}

Including product line and sales rep:

SELECT *
FROM  Customer LEFT JOIN SalesHistory ON Customer.CustID = SalesHistory.CustID
WHERE SalesHistory.CustID IS NULL AND ProductLine = 'XYZ'  AND SalesRep = 'Joe Salesman'

Select allOpen in new window

By 'no sales' I am assuming there are no rows in the SalesHistory table for that CustID and Product Line code.  If there could be rows and still no sales - you will need to be more specific.
Either:

SELECT *
FROM  Customer 
LEFT JOIN SalesHistory 
ON Customer.CustID = SalesHistory.CustID AND ProductLine = 'XYZ'
WHERE SalesHistory.CustID IS NULL AND SalesRep = 'Joe Salesman'

Select allOpen in new window

or

SELECT *
FROM  Customer 
WHERE SalesRep = 'Joe Salesman'
AND NOT EXISTS (SELECT 1 FROM SalesHistory WHERE Customer.CustID = SalesHistory.CustID AND ProductLine = 'XYZ')

Select allOpen in new window

Both have their pro's and con's performance wise - try both to see which works best in your environment.

Thanks to both experts! I tested this and it looks like this is exactly what we needed.

GetRdone,

Glad that helped out - was there a reason for the B grade (something more that should have been explained, etc)?

I didn't realize I did that.. Sorry.... I did find that this works when the salesman is null in the SalesHistory table in a dev environment but this was not the case in my production environment.

Im stuck with the same issue at the moment. Our company sells lots of "ProductLines" the "SalesMan" is almost never going to be NULL on the SalesHist table therefore Im still getting a dataset that has "Customers" im not wanting in my report.

Im back to square one right now.

<<  when the salesman is null  >>

When the Salesman is null... or when the customer is null ?  The queries we posted are looking for records with specific sales reps.

Can you post some representative data along with expected results?

(and you can use the Request Attention button to get this reopened.)

Sure! Here is the sample code Im using in MS Report Builder.
the 07Fox table has the sales history and is the many in the one to many relationship with
FoxCustEntity.  I have never used Subquerries or tempory tables? I wonder if that might apply here.  The resultset that is returned has customers from FoxCustEntity that I do not want to see since they had sales of a specific line of products "PLine" in the sales history table "Fox07".


SELECT
  FoxCustEntity.ID AS [FoxCustEntity ID]
  ,FoxCustEntity.Name
  ,FoxCustEntity.Add1
  ,FoxCustEntity.Add2
  ,FoxCustEntity.City
  ,FoxCustEntity.[State]
  ,FoxCustEntity.Zip
  ,FoxCustEntity.CustSelCode AS [FoxCustEntity CustSelCode]
  ,FoxCustEntity.IRep AS [FoxCustEntity IRep]
  ,FoxCustEntity.ORep AS [FoxCustEntity ORep]
  ,FoxCustEntity.SMgr AS [FoxCustEntity SMgr]
  ,FoxCustEntity.PClass
  ,[07Fox].ID AS [07Fox ID]
  ,[07Fox].InDate
  ,[07Fox].WHS
  ,[07Fox].InvNo
  ,[07Fox].CustName
  ,[07Fox].CustSelCode AS [07Fox CustSelCode]
  ,[07Fox].ProdId
  ,[07Fox].ProdDesc
  ,[07Fox].PLine
  ,[07Fox].PLineDesc
  ,[07Fox].ProdSelCode
  ,[07Fox].SellGroup
  ,[07Fox].QtyShip
  ,[07Fox].CogsEa
  ,[07Fox].ExtCost
  ,[07Fox].UnPrice
  ,[07Fox].ExtPrice
  ,[07Fox].SO
  ,[07Fox].Writer
  ,[07Fox].IRep AS [07Fox IRep]
  ,[07Fox].ORep AS [07Fox ORep]
  ,[07Fox].SMgr AS [07Fox SMgr]
FROM
  FoxCustEntity
  LEFT OUTER JOIN [07Fox]
    ON FoxCustEntity.ID = [07Fox].ID
WHERE
  [07Fox].PLine != @PLine
  AND FoxCustEntity.ORep LIKE @ORep

Hi,

What I was actually looking for was data from your tables (if names, etc are sensitive, use Loony Tunes characters or other junk data)  It doesnt have to be a lot - just enough to illustrate

- What your data looks like
- What you are looking for in terms of query results
- Showing where our suggestions have missed the mark would help too.


I'm heading out for a run - marathon training, so I personally won't be able to check in for several hours, but  lwadwell might be around.

Hi ... I confused.  Your original question said
  >> and for a specific sales rep in the “Customer” table.
but a few comments up you said
  >> when the salesman is null in the SalesHistory table
Yet the SQL you provided seem to use the Customer table.

I actually have to disagree with mbizup's original solution because s/he put the product test in the WHERE clause and hence invalidating the LEFT JOIN.  My suggestion had it in the ON clause to allow the LEFT JOIN to work.  My version would be from your SQL (without the selected column to save space):

SELECT ...
FROM
  FoxCustEntity
  LEFT OUTER JOIN [07Fox]
    ON FoxCustEntity.ID = [07Fox].ID
    AND [07Fox].PLine = @PLine
WHERE
  [07Fox].ID IS NULL
  AND FoxCustEntity.ORep LIKE @ORep

Select allOpen in new window

For your information ... how this works is that the JOIN in the FROM are processed first by the database.  
In the case where records exist in the 07Fox table for a FoxCustEntity record (using the ON predicates) ... they will be returned to the WHERE clause and discarded as [07Fox].ID will not be null.
In the case where NO records exist in the 07Fox table for a FoxCustEntity record ... the FoxCustEntity  will be returned to the WHERE clause but the 07Fox columns will all be null - hence we have found what we want and select it as [07Fox].ID IS null.

The Sales rep ID in the SalesHistory table and will likely never be NULL as this table has rows and rows of records for other items PriceLines "PLines" we sell and the Customer is repeated lots of times in this table. This is truly a one "FoxCustEntity" to many "7Fox" (sales history) relationship. The kicker is I need to select a item from the sales history table "Fox07" in the querry to narrow down to the data results I want. Im not sure this is going to work since I need to narrow down the sales rep ID and the querry looks to be picking this parameter up from records in the sales history table "Fox07" .  I never brought up the NULL and Im not shure how we got here in this thread.

I wonder if I might be better served by trying to add a subreport instead of trying to work out the resultset in 1 querry. I could create a report that gives me all sales for a specific rep and a specific price line then in a sub report give me the customers that are <> to the customer ID in the main report.  I think This is what I'm going to persue at this point and close this issue unless someone has a better idea.

Firstly it was your comment 38308454.

The SQL you showed "AND FoxCustEntity.ORep LIKE @ORep" ... which looks like the ORep column in the FoxCustEntity table ... not the Fox07 table.

Did you try my suggestions?

I am sure that a solution can be found ... this is quite a common (and easy) thing.  But if you have other requirements in the SQL, e.g. "The kicker is I need to select a item from the sales history table" ... we need to be aware as they can affect the solution provided.

This does not work. The suggested querry returns unwanted customers that should not be in the resultset.  If a customer has sales in the SalesHist table I do not want them to show up in the results.

Have a look at this example SQL ... it produces what I believe to be the right result.  Please explain what it does wrong - if anything:

with FoxCustEntity as (
SELECT 1 as id, 'Fred' as ORep union all
SELECT 2 as id, 'Fred' as ORep union all
SELECT 3 as id, 'Bill' as ORep union all
SELECT 4 as id, 'Fred' as ORep
), [07Fox] as (
SELECT 1 as id, 'widgets'    as Pline union all
SELECT 1 as id, 'dodaddies'  as Pline union all
SELECT 1 as id, 'sprockets'  as Pline union all
SELECT 1 as id, 'wammifiers' as Pline union all
SELECT 2 as id, 'dodaddies'  as Pline union all
SELECT 2 as id, 'sprockets'  as Pline union all
SELECT 2 as id, 'wammifiers' as Pline union all
SELECT 3 as id, 'sprockets'  as Pline union all
SELECT 3 as id, 'wammifiers' as Pline union all
SELECT 4 as id, 'widgets'    as Pline union all
SELECT 4 as id, 'dodaddies'  as Pline union all
SELECT 4 as id, 'wammifiers' as Pline
)
SELECT *
FROM
  FoxCustEntity
  LEFT OUTER JOIN [07Fox]
    ON FoxCustEntity.ID = [07Fox].ID
    AND [07Fox].PLine = 'widgets'
WHERE
  [07Fox].ID IS NULL
  AND FoxCustEntity.ORep LIKE 'Fred%'

Select allOpen in new window

This Querry returns 1 record... Fred...Null...Null

Perhaps this approach is wrong for what I'm wanting to do. It seems the problem is the querry to the Fox07 Sales History table returns unwanted ID's to the Customer table and is reporting customers that I do not want a dataset on.

What about a different approach using a subquerry?  The first querry could identify the sales person "ORep" and the PriceLine "PLine" as a True Statement (insteas of false or <>). The subquerry could then select all Customer records that are not = to the ID in the sales hist table.  Any thoughts? Im not familar at all with subquerries so forgive me if this is a dumb thought.

That example query return the only record where the ORep was like 'Fred%' that did not have a 'widgets' PLine.  As there was no 07Fox record ... it returns null instead.  Null is 'no data'.  Are you saying that the nulls are the unwanted ID's?

"Fred" can be in the sales history table 1000 times with another 250+ (Different) "PLines" so it is doubtfull it will ever be NULL.  Im getting Cust ID's returned that we want to filter out of the end resultset but the querry is picking them up in the sales hist table. So even if the argument "Fred" and "Wigets" is ignored the querry is still picking up "Fred" as ORep and any one of hundreds of PLines" "Wigets" "Gatgets" or whatever but the bottom line is the CustomerID  that purchsed the "PLine" product is showing up in the end result querry and should not be, I believe this is because "Fred" has also sold hindreds of other "Plines" and nullifies the argument. There has to be another way to approach this issue.

This last one above might finally do the trick! I need to test this more tomorrow and verify the data results. Also I need to add 2 parameters to narrow down a date range the recordset looks at in Fox07 history table. Where would I add 2 more parameters?
InDate >= @BegDate
InDate <= @EndDate

Thanks

I cannot believe that the last one would work when the left join doesn't.  They are logically equivalent.  I would still like to see the SQL that produces the wrong result.

The extra parameters, if they are columns in [07Fox] must go in the sub-select as it is the only select to access the [07Fox] table.

What is the specific ID number you would like me to retest?

>> What is the specific ID number you would like me to retest?
I did not want you to retest anything.

After a exhaustive search of the data we found that the EntityID may have been a Billing ID rather than a Shipping ID thus giving some false positives in the resultset.  This last SQL did seem to work best and is the one we will use! Thanks to all and especially the expert that had the patience to see this through!  Thanks again! have a great week!