I am creating a view in Sql Server 2005 Managment Studio.
I have three tables.
What I want is one line item per company - returning the Activity Row that has the MAX due date.
This I have accomplished.. But I have some acttivities that have the SAME de date -- because of an system and a timestamp was not placed with the date.
So I want to take the code I am attaching below and add more code that then takes the return from above and where there are multiple rows per company -- I want the row that have the MAX Activity ID ...
dbo.oncd_activity_company.activity_id AS act_id
LEFT OUTER JOIN dbo.oncd_activity_company ON dbo.oncd_company.company_id = dbo.oncd_activity_company.company_id
LEFT OUTER JOIN(SELECT actcomp.company_id, MAX(act.due_date) AS due_date
FROM dbo.oncd_activity_company AS actcomp INNER JOIN
dbo.oncd_activity AS act ON actcomp.activity_id = act.activity_id
GROUP BY actcomp.company_id) AS act_1 ON dbo.oncd_activity_company.company_id = act_1.company_id
LEFT OUTER JOIN dbo.oncd_activity ON dbo.oncd_activity.activity_id = dbo.oncd_activity_company.activity_id AND
dbo.oncd_activity.due_date = act_1.due_date
LEFT OUTER JOIN dbo.oncd_activity_note ON dbo.oncd_activity_note.activity_id = dbo.oncd_activity.activity_id
WHERE (act_1.due_date = dbo.oncd_activity.due_date) OR (dbo.oncd_activity_company.activity_id IS NULL)
ORDER BY dbo.oncd_company.company_name_1