I'm thinking I wouldn't put the sub-query there, especially since it's returning a single scalar value.

you're also using 4 subqueries when you only need 1. Look at this rewrite (not seeing your data - I made some assumptions):

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

The assignment syntax to create Aliases is also being deprecated. You probably want to start training yourself not to use it.