July 27, 2012 at 3:49 pm
SELECT o.Id, o.Name, o.StageName, o.Type, o.Account__c, o.Sales_Person__c, o2.AccountId__c, o2.Sales_Person__c, CRM.dbo.fn_ConvertSFDate(o2.InvoiceDate__c)
AS InvoiceDate
FROM Customers.dbo.Opportunity AS o INNER JOIN
Customers.dbo.Account AS a ON a.Id = o.AccountId INNER JOIN
(SELECT MAX(Id) AS Id, AccountId__c
FROM Customers.dbo.Order__c
WHERE (Sales_Person__c <> '')
GROUP BY AccountId__c) AS o1 ON a.Id = o1.AccountId__c INNER JOIN
Customers.dbo.Order__c AS o2 ON o2.Id = o1.Id
WHERE (o.SalesPerson__c = '') AND (o.Type = 'Renewal') AND (o.StageName NOT LIKE 'Closed%')
This part worked the way I wanted. I wanted it to pull the Sales_Person__c field from the most recent order (Order__c) if it isn't blank.
But if that field is blank in the order then I need it to look at the Sales_Person field in the most recent opportunity that has the status Closed Won.
So the bottom line is I need it to display the Sales_Person__c field with the most recent order if the sales person field is not blank in the order object, but if it is blank then fill it in based off the most recent opportunity that has the status closed won. I'm not sure how to add it to my current query.
I apologize if I'm not wording this correctly. Any help would be greatly appreciated. Thank you.
July 27, 2012 at 3:59 pm
If you could post the DDL (CREATE TABLE statement) for each of the tables, some SAMPLE data (meaning something made up that properly represents the problem domain) as a series of INSERT INTO statements for each table (5 or 6 rows max), expected results based on the sample data.
July 27, 2012 at 4:28 pm
Sample data along with sample tables in order to properly frame possible solutions for this problem would be very helpful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply