Here is my recommendation:

```
SELECT r.* -- Replace this with actual column names
, NoOfDaysOpen = datediff(DAY, r.Closed, r.Created)
FROM dbo.Activity a
INNER JOIN dbo.ActivityEntry ae ON ae.ActivityID = a.ID
AND ae.ID = a.LastActivityEntryId
CROSS APPLY (
SELECT UniqueReference = max(CASE WHEN aed.[Key] = 'UniqueReference' THEN aed.[Value] END)
, VisitNumber = max(CASE WHEN aed.[Key] = 'Visit.Current.Number' THEN aed.[Value] END)
, VisitReference = max(CASE WHEN aed.[Key] = 'Visit.Current.Reference' THEN aed.[Value] END)
, VisitDate = max(CASE WHEN aed.[Key] = 'Visit.Current.Report.VisitDate' THEN convert(date, nullif(aed.[Value], '')) END)
, Created = max(CASE WHEN aed.[Key] = 'Created' THEN convert(date, nullif(aed.[Value], '')) END)
, Closed = max(CASE WHEN aed.[Key] = 'Closed' THEN convert(date, nullif(aed.[Value], '')) END)
, AssignmentNumber = max(CASE WHEN aed.[Key] = 'Assignment.Number' THEN aed.[Value] END)
, ReportTemplateName = max(CASE WHEN aed.[Key] = 'ActivityType' THEN aed.[Value] END)
, CustomerName = max(CASE WHEN aed.[Key] = 'Customer.Address' THEN aed.[Value] END)
, ProjectAssignment = max(CASE WHEN aed.[Key] = 'EVONumber' THEN aed.[Value] END)
, OperatingProjectCoordinator = max(CASE WHEN aed.[Key] = 'Intertek.Contract.Project.Coordinator' THEN aed.[Value] END)
, IntertekProjectNumber = max(CASE WHEN aed.[Key] = 'Intertek.ProjectNumber' THEN aed.[Value] END)
, SupplierName = max(CASE WHEN aed.[Key] = 'Supplier.Location.SiteName' THEN aed.[Value] END)
, SubSupplierName = max(CASE WHEN aed.[Key] = 'SubSupplier.Location.SiteName' THEN aed.[Value] END)
, TSName = max(CASE WHEN aed.[Key] = 'TSName' THEN aed.[Value] END)
, TSPin = max(CASE WHEN aed.[Key] = 'TechnicalSpecialist.PIN' THEN aed.[Value] END)
, TSEntity = max(CASE WHEN aed.[Key] = 'TSEntity' THEN aed.[Value] END)
, TSEntityName = max(CASE WHEN aed.[Key] = 'TSEntityName' THEN aed.[Value] END)
, QualityCategory = max(CASE WHEN aed.[Key] = 'QualityCategory' THEN aed.[Value] END)
, QualityComments = max(CASE WHEN aed.[Key] = 'QualityComments' THEN aed.[Value] END)
, QualityCriticality = max(CASE WHEN aed.[Key] = 'QualityCriticality' THEN aed.[Value] END)
, QualityEquipment = max(CASE WHEN aed.[Key] = 'QualityEquipment' THEN aed.[Value] END)
, QualityItemNumber = max(CASE WHEN aed.[Key] = 'QualityItem.Number' THEN aed.[Value] END)
, QualityObsType = max(CASE WHEN aed.[Key] = 'QualityObsType' THEN aed.[Value] END)
, QualitySubCategory = max(CASE WHEN aed.[Key] = 'QualitySubCategory' THEN aed.[Value] END)
FROM dbo.ActivityEntryData aed
WHERE aed.ActivityEntryId = a.LastActivityEntryId
) r
WHERE a.ActivityType = 'QualityObservationReport'
```

You shouldn't need DISTINCT at all - if you have duplicates then there must be something else you need to filter on to eliminate the duplicates.

What this does is uses CROSS APPLY to query the ActivityEntryData table for the associated LastActivityEntryID and CROSS-TABs the results. That gives us all of the columns you want in a single query - that can then be used in the SELECT with a simple DATEDIFF.

This should at least give you a good start...