?
Solved

Report on customers with NO sales for specific product line

Posted on 2012-08-17
25
Medium Priority
?
711 Views
Last Modified: 2012-08-29
Experts I'm working with SQL Server 2008r2 and MS Report Builder 3.0
Our office is wanting what is turning out to be a complex report for me to construct.

Info: I have 2 tables “Customer” and “SalesHistory” both files have a key field “CustID”

The office is wanting a report that will identify all Customer with NO sales for a specific product line we sell in the “SalesHistory” table and for a specific sales rep in the “Customer” table.

I'm stumped here, can anyone offer some suggestions on how I might make this work?
Thanks in Advance
0
Comment
Question by:GetRdone
  • 10
  • 9
  • 5
24 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38307098
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}
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38307100
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'

Open in new window

0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38307360
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'

Open 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')

Open in new window

Both have their pro's and con's performance wise - try both to see which works best in your environment.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GetRdone
ID: 38307932
Thanks to both experts! I tested this and it looks like this is exactly what we needed.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38308033
GetRdone,

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

Author Comment

by:GetRdone
ID: 38308454
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38308468
<<  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.)
0
 

Author Comment

by:GetRdone
ID: 38308500
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38308520
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.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38308568
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

Open in new window

0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38308584
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.
0
 

Author Comment

by:GetRdone
ID: 38308594
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.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38308616
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.
0
 

Author Comment

by:GetRdone
ID: 38308763
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.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38308782
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%'

Open in new window

0
 

Author Comment

by:GetRdone
ID: 38309477
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.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38310097
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?
0
 

Author Comment

by:GetRdone
ID: 38310389
"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.
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38310408
Show me the query you are running that produces the wrong result.

But I believe that the other rows should not be selected because of the "WHERE [07Fox].ID IS NULL" test ... which work ONLY when it is LEFT JOIN.  If it was an INNER JOIN ... I would 100% agree with you.

Another alternative is the one I gave originally (which you did try?):
SELECT *
FROM  FoxCustEntity
WHERE FoxCustEntity.ORep LIKE @ORep
AND NOT EXISTS (SELECT 1 FROM [07Fox] WHERE FoxCustEntity.ID = [07Fox].ID AND  [07Fox].PLine = @PLine)

Open in new window

0
 

Author Comment

by:GetRdone
ID: 38310462
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
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38310472
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.
0
 

Author Comment

by:GetRdone
ID: 38311523
What is the specific ID number you would like me to retest?
0
 
LVL 25

Expert Comment

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

Author Closing Comment

by:GetRdone
ID: 38345364
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!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question