May 4, 2012 at 8:44 am
SELECT l.Id, CRM.dbo.fn_ConvertSFDateTime(e.CreatedDate) AS lastevaluationdate__c, e.ProductId__c AS lastevaluationproduct__c
FROM Customers.dbo.Evaluation__c AS e INNER JOIN
Customers.dbo.Lead AS l ON l.Email = e.Email__c
WHERE (l.IsDeleted = 0) AND (l.IsConverted = 0) AND (COALESCE (l.Last_Evaluation_Product__c, '') = '') AND (e.ProductId__c > '')
I need to capture the most recent created date from the evaluation table, but I can't seem to get it to work correctly. I tried putting MAX in front of the e.CreatedDate, but got an error "Column 'l.Id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Same for e.ProductId__c
Any help would be greatly appreciated!
May 4, 2012 at 8:50 am
littlelisa1111 (5/4/2012)
SELECT l.Id, CRM.dbo.fn_ConvertSFDateTime(e.CreatedDate) AS lastevaluationdate__c, e.ProductId__c AS lastevaluationproduct__cFROM Customers.dbo.Evaluation__c AS e INNER JOIN
Customers.dbo.Lead AS l ON l.Email = e.Email__c
WHERE (l.IsDeleted = 0) AND (l.IsConverted = 0) AND (COALESCE (l.Last_Evaluation_Product__c, '') = '') AND (e.ProductId__c > '')
I need to capture the most recent created date from the evaluation table, but I can't seem to get it to work correctly. I tried putting MAX in front of the e.CreatedDate, but got an error "Column 'l.Id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Same for e.ProductId__c
Any help would be greatly appreciated!
depending on what you want to do there are 2 solutions
do you want the same max(e.createddate) on every row? or do you want the max for that productid/i.id combination?
if its the first then
declare @maxdate datetime
select @maxdate=max(createdate) from.....
select ....,@maxdate from ....
if it's the second then you need to add in a "group by" clause
MVDBA
May 4, 2012 at 8:55 am
I need the max date for each productid/l.id
May 4, 2012 at 9:06 am
so the format of your query is
select x,y,max(z)
from mytable
where x<5
GROUP BY x,y
you must include all columns (except those you are aggregating) into the group by clause
MVDBA
May 4, 2012 at 9:10 am
SELECT l.Id
, MAX(CRM.dbo.fn_ConvertSFDateTime(e.CreatedDate)) AS lastevaluationdate__c
, e.ProductId__c AS lastevaluationproduct__c
FROM Customers.dbo.Evaluation__c AS e
INNER JOIN Customers.dbo.Lead AS l ON l.Email = e.Email__c
WHERE (l.IsDeleted = 0)
AND (l.IsConverted = 0)
AND (COALESCE (l.Last_Evaluation_Product__c, '') = '')
AND (e.ProductId__c > '')
GROUP BY l.Id,e.ProductId__c
MVDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply