...
| Code Block |
|---|
CREATE VIEW dbo.PW001SRV701 as
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
from PW001U02 VAC
left join pw001p01 p01 on p01.pin=vac.pin
left join pworg ves on ves.numorgid=vac.ufield3
left join pw001c02 rank on rank.code=vac.Ufield5
where vac.ufield6='COMP' |
...