select   EmpID as [Employee_ID] from dbo.Employee group by EmpID 

select distinct  EmpID as [Employee_ID] from dbo.Employee

declare @ASSGNID int
--calculate the scalar
select @ASSGNID=ASSGNID from MaintDB..Project (nolock)  where pcode = '11224DER'

select [ASSGNID] = @assgnID,   --use the scalar
[COUNTRY] = upper(c.FullName),
[Branch NUMBER] = s.Branchno, 
[RECORD ID] = d.uid,
[INITIALS] = d.initials,
[DOB] = Customer.dbo.function_DateFormat(d.dob,'mdy','',0),
[EXAMNUM] = d.Custid,
vst.[EXAMDATE] ,
[ENTRY] = r.ENTRYno,
vst.[ENTRYDATE]  ,
vst.[EXAMFAILCAUSE] ,
vst.[EXAMFAILDATE]  ,
vst.[EARLYTERMCAUSE],
vst.[EARLYTERMDATE] ,
[VISITRECID] = 'LQ1' + cast(v.VID as nvarchar),
[VISITNAME] = rv.VisitCode,
[VISITCOMPDATE] = Customer.dbo.function_DateFormat(v.Datevisited,'mdy','',0)
from
Customer.dbo.Customer_Geo d (nolock) inner join
MaintDB.dbo.Branch s (nolock) on d.Siteid = s.BranchID inner join
MaintDB.dbo.country c (nolock) on s.countryid = c.CountryID left join
Customer.dbo.Customer_ROW r (nolock) on d.Custid = r.Custid left join
--collapse all 4 subs into just one
	(
	select CustID, 
		Customer.dbo.function_DateFormat(
				max(case when ID_Visit = 1 then Datevisited else null end)) as EXAMDATE,
		Customer.dbo.function_DateFormat(
			max(case when ID_Visit = 3 then Datevisited else null end)) as ENTRYDATE,
		Customer.dbo.function_DateFormat(
			max(case when ID_Visit = 20 then Datevisited else null end)) as EXAMFAILDATE,
		Customer.dbo.function_DateFormat(
			max(case when ID_Visit = 21 then Datevisited else null end)) as EARLYTERMDATE,
		max(case when ID_Visit = 20 then CAUSEID else null end) as EXAMFAILCAUSE,
		max(case when ID_Visit = 21 then CAUSEID else null end) as EARLYTERMCAUSE
	from Customer.dbo.Customer_Visited (nolock)
	where ID_visit in (1,3,20,21)
	group by CustID
	) vst
			on d.Custid = vst.Custid left join
Customer.dbo.Customer_Visited v (nolock) on d.Custid = v.Custid left join
Customer.dbo.Customer_Type rv (nolock) on v.ID_Visit = rv.ID_Visit
order by S.Branchno,d.Custid, v.VID