How to get most-recent data by date

1

Hi. I'm a newbie who's just started using SQL and I'm trying to build a report which looks at the most-recent activity completion date (Task.CompletionDate), and then pulls back the most-recent creator of that particular activity (ActivityCreator.Fullname).

Here is the code:

SELECT
ActivityCreator.FullName as 'Activity_Creator'
,crm.Task.CompletionDate
,crm.Task.CreatedByID

CASE
WHEN ActivityCreator.FullName = 'Person 1' THEN 'Team 1'
WHEN ActivityCreator.FullName = 'Person 2' THEN 'Team 2'
WHEN ActivityCreator.FullName = 'Person 3' THEN 'Team 3'
END AS 'Activity_Owner_Team'

FROM
INNER JOIN crm.Users ActivityCreator
on ActivityCreator.UserID = crm.Task.CreatedByID

WHERE ActivityCreator.FullName IN ('Person 1', 'Person 2', 'Person 3')

Any help would be gratefully appreciated.

Thanks in advance,

2

SELECT
ActivityCreator.FullName as 'Activity_Creator'
,crm.Task.CompletionDate
,crm.Task.CreatedByID
, -- << comma is missing in your query
CASE
WHEN ActivityCreator.FullName = 'Person 1' THEN 'Team 1'
WHEN ActivityCreator.FullName = 'Person 2' THEN 'Team 2'
WHEN ActivityCreator.FullName = 'Person 3' THEN 'Team 3'
END AS 'Activity_Owner_Team'

FROM crm.? -- Table name is missing here
INNER JOIN crm.Users ActivityCreator
on ActivityCreator.UserID = crm.Task.CreatedByID

WHERE ActivityCreator.FullName IN ('Person 1', 'Person 2', 'Person 3')

3

with tbl as (
SELECT
ActivityCreator.FullName as 'Activity_Creator'
,crm.Task.CompletionDate
,crm.Task.CreatedByID
, -- << comma is missing in your query
CASE
WHEN ActivityCreator.FullName = 'Person 1' THEN 'Team 1'
WHEN ActivityCreator.FullName = 'Person 2' THEN 'Team 2'
WHEN ActivityCreator.FullName = 'Person 3' THEN 'Team 3'
END AS 'Activity_Owner_Team'
row_number() over(partition by createdbyid order by CompletionDate Desc) Rn

FROM crm.? -- Table name is missing here
INNER JOIN crm.Users ActivityCreator
on ActivityCreator.UserID = crm.Task.CreatedByID

WHERE ActivityCreator.FullName IN ('Person 1', 'Person 2', 'Person 3')
)
select
[Activity_Creator], CompletionDate, CreatedByID, [Activity_Owner_Team] from from tbl where Rn=1;

4

Works an absolute treat! Thanks a million, jotorre_riversidedpss.org :slight_smile: