selectCREATE VIEW dbo.PW001SRV134 AS
SELECT p01.pin as PIN AS PIN,
P01.ALTERNATIVEPIN AS 'ALTERNATIVE PIN',
CASE
WHEN p01.sex = 'M' THEN 'Mr'
WHEN p01.sex = 'F' THEN 'Mrs'
END AS Gender,
p01.name as Name, p01.NAME AS 'Full Name',
p01.FIRSTNAME AS 'First Name',
p01.MIDDLENAME AS 'Middle Name',
p01.LASTNAME AS 'Last Name',
dateaddDATEADD(yy,datediff DATEDIFF(yy, p01.birthdate,getdate GETDATE()), birthdate) 'Celebration_ Date',
MonthMONTH(p01.birthdate) as AS 'Month Nr',
, DateName(mm,DateaddDATENAME(
mm,
DATEADD(yy,datediff DATEDIFF(yy, p01.birthdate,getdate GETDATE()), p01.birthdate)
) Month, MONTH,
CONVERT(
INT,
DATENAME(
d,
DATEADD(YY, DATEDIFF(YY, P01.BIRTHDATE, GETDATE()), BIRTHDATE)
)
) AS DateDATE,
DateName(wk,Dateadd DATENAME(
wk,
DATEADD(yy,datediff DATEDIFF(yy, p01.birthdate,getdate GETDATE()), birthdate)
) Week,
DateName(weekday,DateaddDATENAME(
weekday,
DATEADD(yy,datediff DATEDIFF(yy, p01.birthdate,getdate GETDATE()), birthdate)
) Day DAY,
Datediff DATEDIFF(yy, p01.birthdate,getdate GETDATE()) Age,
p01.Birthdate,
p01.COUNTRYOFBIRTH + ' - ' + COUNTRY.COUNTRYNAME AS 'Country / Location',
PCAB.Cabin,
POS.NAME as Position,
AS Position,
P0T.TELENO AS Email,
mob.Teleno AS 'Mobile Phone',
p01.RANK AS 'Current Rank',
p01.Client numorgid,
VSL.NUMORGID AS vessel_numorgid,
p01.EmploymentStartDate,
p01.EmploymentEnddate,
from dbo.pw001p01 p01
JOIN dbo.PW001P03 P03
p01.COSTPLACE AS 'Department/Cost Place Code',
c43.[NAME] AS 'Department/Cost Place'
FROM dbo.pw001p01 p01
JOIN dbo.PW001P03 P03
ON P01.PIN = P03.PIN
AND ISNULL(P03.PLANNED,'N') <> 'Y'
AND (P03.DATETO IS NULL orOR P03.DATETO >= GETDATE())
AND P03.DATEFROM <= GETDATE()
LEFT JOIN Pw001C12 act
ON act.code = p03.code
AND P03.DATEFROM <= GETDATE() LEFt JOIN Pw001C12 act on act.code=p03.code
JOIN PWORG VSL PWORG VSL
ON VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3)
LEFT JOIN dbo.PWORG DEP
ON DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4)
LEFT JOIN dbo.PWORG POS
ON POS.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 5)
LEFt LEFT JOIN dbo.PWORGVESCAB PCAB
ON PCAB.CABINID = p03.cabinid
where act.OPTIONS like '%S%'
LEFT JOIN PWCOUNTRY COUNTRY
ON P01.COUNTRYOFBIRTH = COUNTRY.COUNTRYCODE
LEFT JOIN PW001C43 c43
ON c43.CODE = p01.COSTPLACE
LEFT JOIN PW001P0T P0T
ON (
(P01.PIN = P0T.PIN)
AND (P0T.TELETYPE = 6)
AND (NOT P0T.TELEPRIORITY IS NULL)
AND NOT EXISTS
(
SELECT SEQUENCENO
FROM PW001P0T P0T2
WHERE (P0T.PIN = P0T2.PIN)
AND (P0T2.TELETYPE = 6)
AND (
(P0T2.TELEPRIORITY < P0T.TELEPRIORITY)
OR (
(P0T2.TELEPRIORITY = P0T.TELEPRIORITY)
AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)
)
)
)
)
LEFT JOIN PW001P0T mob
ON (
(P01.PIN = mob.PIN)
AND (mob.TELETYPE = 3)
AND (NOT mob.TELEPRIORITY IS NULL)
AND NOT EXISTS
(
SELECT SEQUENCENO
FROM PW001P0T mob2
WHERE (mob.PIN = mob2.PIN)
AND (mob2.TELETYPE = 3)
AND (
(mob2.TELEPRIORITY < mob.TELEPRIORITY)
OR (
(mob2.TELEPRIORITY = mob.TELEPRIORITY)
AND (mob2.SEQUENCENO < mob.SEQUENCENO)
)
)
)
)
WHERE act.OPTIONS LIKE '%S%'
|