Getting names, based on date and user

Question

I am new to SQL (in Salesforce) and I have some problems with a query. I want to get the customer of the last sold item, for each user. (“John’s last sale was to customer Jane”).

We have built everything around the Opportunity object. If the opportunity object is staged as ‘closed won’, it is counted as a sell. Hence, my approach is to find objects with the ‘highest’ date, for each seller, which is staged as ‘closed won’. After that, I find the object’s customer which is associated with the date I found. However, it does not work.

SELECT o1.CustomerName 
FROM Opportunity o1 
WHERE o1.CloseDate IN(
 SELECT MAX(o2.CloseDate) 
 FROM Opportunity o2 
 WHERE o2.StageName = 'Closed Won' 
 GROUP BY o2.UserId)

The inner query itself generates a list with the accurate 'last close date' for each user. The tables I have are, heavily simplified:

Opportunity(CustomerName, StageName, CloseDate, UserId)

User(UserId, UserName)

Does anyone have any ideas on a correct query, or a better approach?

Seems like you want a correlated sub-query. Include o1 condition in it! — jarlh, Jun 30 '16 at 14:14
Salesforce does not use SQL unless you are using the WSDL. Are you looking for a SOQL which is much different than SQL — coder32, Jun 30 '16 at 14:15

score 0 · Answer 1 · answered Jun 30 '16 at 14:14

You actually want a correlated subquery. The problem is that the dates are not tied to the users, so you are getting all records for users, where the date is the maximum date for any user.

Here is an alternative method:

SELECT o1.CustomerName 
FROM Opportunity o1 
WHERE o1.CloseDate IN (SELECT MAX(o2.CloseDate) 
                       FROM Opportunity o2 
                       WHERE o2.StageName = 'Closed Won' 
                             o1.UserId = o2.UserId
                      );

You may want to add the StageName = 'Closed Won' condition to the outer query as well.

Missing and in the where statement of the subquery – Chris Jun 30 '16 at 14:57 — Chris, Jun 30 '16 at 14:57

Getting names, based on date and user

1 Answers1