Problem is here-
SELECT DISTINCT TOP 1 HR.TerritoryNum TerritoryID,HR.Dateto,HR.DateFrom
FROM HistoryRepTerritory HR
where e.Datesent between HR.DateFrom and HR.DateTo
and HR.RepID = r.RepID
You can mention only one column for the subquery.
Try this-
select COUNT(distinct RepID) 'MissingInventory'
from
( select CAST(datediff(day,0,datesent) as datetime) datesent
,r.repid,TerritoryHierarchy = (SELECT DISTINCT TOP 1 HR.TerritoryNum
FROM HistoryRepTerritory HR
where e.Datesent between HR.DateFrom and HR.DateTo
and HR.RepID = r.RepID
)
from dbo.reps r inner join dbo.Email_EmailsToSend e on r.RepId = e.Reference
where e.AlertsituationId = '13'
and cast(Datediff(DAY,0,cast(isnull(e.Datesent,'1/1/1900') as datetime)) as datetime)
between '1/1/2014' and '12/31/2014') tf
WHERE EXISTS( SELECT 1 FROM TerritoryHierarchy TH inner join HistoryRepTerritory HT
on HT.TerritoryNum = TH.ID )
Hope, it helps :)