CREATE VIEW dbo.PW001SRV701 asselectvacas
select
	vac.PIN,
	vac.Ufield4 'Vacancy ID',
	vac.UField1 as 'Vacancy Date',
	vac.UField2 as 'Vacancy Actual  Date',
	ves.Name as 'Vacancy Vessel',
	rank.Name as 'Vacancy Position',
	case when vac.ufield6='OP' then 'Open' 
		 when vac.ufield6='COMP' then 'Completed'  
		 when vac.ufield6='CANC' then 'Canceled' 
		 else '' 
	end as 'Vacancy Status',
	vac.UField7 as 'Vacancy Completion/Cancellation Date',
	vac.UField11 as 'Agency Fee',
	vac.UField12 as 'Agency Comment',
	vac.UField13 as 'Recruitment Fair Name',
	vac.UField14 as 'Recruitment Fair Date',
	vac.UField15 as 'Recruitment Fair Comment',
	vac.UField16 as 'SMC Name',
	vac.UField17 as 'SMC Date',
	vac.UField18 as 'SMC Comment',
	vac.UField19 as 'SMC Facebook',
	vac.UField20 as 'SMC Instagram',
	vac.UField21 as 'SMC LinkedIn',
	vac.UField22 as 'SMC Other',
	vac.UField23 as'SMC Channel Comments',
	p01.Employmentstartdate,
	p01.Employmentenddate,
	p01.Client as Numorgid
	
Numorgidfromfrom PW001U02 VACleftVAC
left join pw001p01 p01 on p01.pin=vac.pinleftpin
left join pworg ves on ves.numorgid=vac.ufield3leftufield3
left join pw001c02 rank on rank.code=vac.Ufield5whereUfield5
where vac.ufield6='COMP'