July 23, 2012 at 10:20 am
select o.Id, o.AccountId, o.Name, o.StageName,o.[type], o.Account__c,o.SalesPerson__c, c.InvoiceDate__c,c.Sales_Person__c
from customers.dbo.opportunity as o inner join
customers.dbo.orderheader__c as c on c.AccountId__c = o.AccountId
Where o.SalesPerson__c = '' and o.[Type] = 'Integration Renewal' and o.StageName NOT LIKE 'Closed%' and c.Sales_Person__c <> ''
My goal is to update the o.SalesPerson__c field with the information that is based on the c.Sales_Person__c in most recent invoice date for the account (there are multiple invoice dates for the accounts)
Any help would be greatly appreciated.
I am assuming I would use something like MAX(c.InvoiceDate__c) but I can't get it to work without a group by clause error.
July 23, 2012 at 10:24 am
littlelisa1111 (7/23/2012)
select o.Id, o.AccountId, o.Name, o.StageName,o.[type], o.Account__c,o.SalesPerson__c, c.InvoiceDate__c,c.Sales_Person__cfrom customers.dbo.opportunity as o inner join
customers.dbo.orderheader__c as c on c.AccountId__c = o.AccountId
Where o.SalesPerson__c = '' and o.[Type] = 'Integration Renewal' and o.StageName NOT LIKE 'Closed%' and c.Sales_Person__c <> ''
My goal is to update the o.SalesPerson__c field with the information that is based on the c.Sales_Person__c in most recent invoice date for the account (there are multiple invoice dates for the accounts)
Any help would be greatly appreciated.
I am assuming I would use something like MAX(c.InvoiceDate__c) but I can't get it to work without a group by clause error.
Look at using a CTE and the row_number() function. See what you can come up with using these hints.
July 23, 2012 at 11:46 am
Are you just trying to Select the correct salesperson from your orderheader__c table, or do you want to actually update your opportunity table? The query is just a Select, but your question mentions updating.
Usually, for things like "most recent X for Y" (most recent salesperson for customer, most recent order for customer, most recent invoice for order, etc.), I recommend doing that with a Select instead of an update, and just get the data at runtime instead of storing it in a table. So, unless there's a reason otherwise, I'd do this as a Select with a Cross/Outer Apply.
If you do it as a Select, it might look like this:
SELECT o.Id,
o.AccountId,
o.Name,
o.StageName,
o.[type],
o.Account__c,
o.SalesPerson__c,
c.InvoiceDate__c,
c.Sales_Person__c
FROM customers.dbo.opportunity AS o
CROSS APPLY (SELECT TOP (1)
c.InvoiceDate__c,
c.Sales_Person__c
FROM customers.dbo.orderheader__c AS c
WHERE c.AccountId__c = o.AccountId
AND c.Sales_Person__c > ''
ORDER BY c.InvoiceDate__c DESC) AS c
WHERE o.SalesPerson__c = ''
AND o.[Type] = 'Integration Renewal'
AND o.StageName NOT LIKE 'Closed%' ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply