Showing users that have created 0 records

• a2attard

  Grasshopper

  Points: 23

  More actions

  April 2, 2017 at 10:31 am

  #320067

  Hi,

  I'm working with a mirror of salesforce data in SQL Server 2012 and I am at the moment generating a report that shows the # of leads that were created by X user. This is easy enough to do by just doing a SELECT for the lead creator & the num of leads and creating a pivot table in excel.

  However, I would also like some report to show me which active users (which I could join from a separate user table) have created 0 leads in the last week. Is there an easy way to do this?

  My first thought was doing something like...taking the total list of active users, then comparing it against the list of users that did generate something and where they are not found, I'd have my list, but that would be very manual - ideally would like to build it into the query somehow.

• pietlinden

  SSC Guru

  Points: 63597

  More actions

  April 2, 2017 at 11:58 am

  #1936179

  Sounds like you are trying to do this with an INNER JOIN instead of an OUTER JOIN.  If you right-click on the join between the two tables, you can change it. (Or just edit the SQL). An outer join will keep nulls on the many (sales) side of the join.. Instead of something like this:

  SELECT Salesperson.FullName, SUM(Sales.SalesAmount)
FROM Salesperson sp INNER JOIN Sales s ON sp.SalespersonID = s.SalespersonID
GROUP BY Salesperson.FullName;
  you want
  SELECT Salesperson.FullName, SUM(Sales.SalesAmount)
FROM Salesperson sp LEFT JOIN Sales s ON sp.SalespersonID = s.SalespersonID
GROUP BY Salesperson.FullName;

  The second will show all sales people, regardless of whether they have sales.

• a2attard

  Grasshopper

  Points: 23

  More actions

  April 2, 2017 at 2:24 pm

  #1936183

  Hey pietlinden,

  Thanks a lot for the quick reply. I don't think that wilil work for me but I think I did a poor job of explaining. This should help to clear it up:
  

• Jeff Moden

  SSC Guru

  Points: 1004646

  More actions

  April 2, 2017 at 5:39 pm

  #1936190

  It would be much easier to help you resolve this if you provided some readily consumable test data.  See the first link in my signature line for how to do that.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Thom A

  SSC Guru

  Points: 99143

  More actions

  April 3, 2017 at 2:20 am

  #1936217

  May I ask why you think a LEFT JOIN won't work for you? What's is your reasoning, have you tested?

  Have a look at this simple example. Note that in the sales no Chocolate biscuits are sold, yet in the second query, with the LEFT JOIN, they are shown. Is this not what you are after?
  CREATE TABLE #Item
  (ItemID INT IDENTITY(1,1),
   ItemName VARCHAR(20),
   ItemPrice decimal(8,2));

  CREATE TABLE #Sale
  (SaleID INT IDENTITY(1,1),
  ItemID INT);
GO

  INSERT INTO #Item (ItemName, ItemPrice)
VALUES
  ('Apple', 0.4),
  ('Banana', 0.35),
  ('Orange', 0.5),
  ('Chicken Soup', 1.10),
  ('Chocolate Biscuits', 1.25);
GO

  INSERT INTO #Sale (ItemID)
VALUES
  (1),(2),(1),(1),(3),(4),(4);
GO

  SELECT I.ItemName,
       COUNT(S.SaleID) AS Sales, SUM(I.ItemPrice) AS SalesValue
FROM #Item I
  JOIN #Sale S ON I.ItemID = S.ItemID
GROUP BY I.ItemName;

  --Now with a LEFT JOIN
SELECT I.ItemName,
       COUNT(S.SaleID) AS Sales, SUM(CASE WHEN S.SaleID IS NOT NULL THEN I.ItemPrice ELSE 0 END) AS SalesValue
FROM #Item I
  LEFT JOIN #Sale S ON I.ItemID = S.ItemID
GROUP BY I.ItemName;

  GO

  DROP TABLE #Item;
DROP TABLE #Sale;


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk