Stuff

Select 	MAX(unsys.UnitSystemKey) AS usKey,
			unsys.UnitID1,			
			cust.CustomerNo,
			cust.AccountName,
			unsys.FirstActivatedDate
--			emp.EmployeeCode
FROM 	acUnitSystems unsys
		LEFT JOIN acUnits unit
			ON unsys.UnitKey = unit.UnitKey
		LEFT JOIN acContracts contract
			ON unit.ContractKey = contract.ContractKey
		LEFT JOIN CustomerInfoVW cust
			ON contract.CustomerKey = cust.CustomerKey
		LEFT JOIN acUnitProvParams upp
			ON unsys.UnitSystemKey = upp.UnitSystemKey 		
		LEFT JOIN acSysTypeVersionParams param
			ON upp.SysTypeVersionParamKey = param.SysTypeVersionParamKey
WHERE unsys.SystemKey = 1
	AND unsys.Status = 'A'
	AND param.ParameterName = 'Voice_Mail'
	AND unsys.UnitID1 NOT IN
		(
			SELECT us.UnitID1 
			FROM acUnitSystems us
				INNER JOIN acRatePlans rp
					ON us.RatePlanKey = rp.RatePlanKey
			WHERE us.SystemKey = 2
				AND rp.RatePlan IN ('Z1', 'Z2', 'SPZ1', 'SPZ2')
		)
GROUP BY cust.CustomerNo, 			
			cust.AccountName,
			unsys.FirstActivatedDate,
			unsys.UnitID1
ORDER BY cust.CustomerNo