Marketing Cloud Query Returning Multiple Records

Question

I have a Marketing Cloud query that returns records from synchronized data extensions. The query is looking for recent appointments scheduled. The query returns duplicate appointments (where the record is exactly the same as another). What I am trying to do return only one record. Not sure why it is returning more than one record for the same Salesforce event. Happy for any insight into my query.

SELECT Distinct
o.CreatedDate,
o.StageName,
e.Id, 
e.OwnerId, 
e.Subject,
e.WhoId, 
e.StartDateTime,datediff(minute,GETDATE(),e.StartDateTime)/60 AS HoursCountDown,
e.IsDeleted,
e.WEGP1_MeetingResult__c,
e.WEGP1_MeetingType__c,
e.ActivityDateTime as StartDate,
e.WhatId,
c.Classification__c,
u.Name as FromName,
u.Email as FromEmail
 
FROM [Event_Salesforce] e
JOIN [Opportunity_Salesforce] as o on o.OwnerId = e.OwnerId
Left JOIN [Contact_Salesforce] as c on c.Id = e.WhoId
JOIN [User_Salesforce] as u on u.Id = e.OwnerId

WHERE o.CreatedDate >= DATEADD(DAY, -1, GETDATE()) 
and e.WhoId is not null
and e.ActivityDateTime is not null
and e.IsDeleted = 'False'
and e.WEGP1_MeetingType__c = 'Needs Analysis'
and (e.[StartDateTime] > CONVERT(DATE, GETDATE())) AND (e.[StartDateTime] < CONVERT(DATE, DATEADD(DAY, 15, GETDATE())))

Answer 1

You didn't mention the fields that comprise the primary key in your target Data Extension.

If you're just wishing to de-duplicate, I'd suggest a partition, where you can add criteria for choosing which of the duplicates to return:

SELECT top 1 with ties
  o.CreatedDate
, o.StageName
, e.Id
, e.OwnerId
, e.Subject
, e.WhoId
, e.StartDateTime,datediff(minute,getDate(),e.StartDateTime)/60 AS HoursCountDown
, e.IsDeleted
, e.WEGP1_MeetingResult__c
, e.WEGP1_MeetingType__c
, e.ActivityDateTime as StartDate
, e.WhatId
, c.Classification__c
, u.Name as FromName
, u.Email as FromEmail
FROM [Event_Salesforce] e
inner JOIN [Opportunity_Salesforce] as o on o.OwnerId = e.OwnerId
left JOIN [Contact_Salesforce] as c on c.Id = e.WhoId
inner JOIN [User_Salesforce] as u on u.Id = e.OwnerId
WHERE 1=1
and o.CreatedDate >= convert(date, getDate()-1)
and e.WhoId is not null
and e.ActivityDateTime is not null
and e.IsDeleted = 'False'
and e.WEGP1_MeetingType__c = 'Needs Analysis'
and e.[StartDateTime] > CONVERT(DATE, getDate())) 
AND e.[StartDateTime] < CONVERT(DATE, getDate()+15)
order by row_number() over (partition by e.whoid order by e.ActivityDateTime desc)

The partition by e.whoid indicates duplicates are by e.whoid and the order by e.ActivityDateTime desc orders the duplicates by e.whoid from newest to oldest. The top 1 with ties chooses the first of the duplicates.

Note: Query Studio won't parse queries like this, but it'll work fine as a Query Activity.