CREATE VIEW dbo.PW001SRV50PW001SRV134 AS
SELECT p01.pin AS selectPIN,
p01.pin asP01.ALTERNATIVEPIN AS 'ALTERNATIVE PIN',
CASE
case whenWHEN p01.sex = 'M' thenTHEN 'Mr'
whenWHEN p01.sex = 'F' thenTHEN 'Mrs'
END end as AS Gender,
p01.NAME as AS 'Full Name',
p01.FIRSTNAME as AS 'First Name',
p01.MIDDLENAME as AS 'Middle Name',
p01.LASTNAME as 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,DateaddDATENAME(
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,
DatediffDATEDIFF(yy, p01.birthdate,getdate GETDATE()) Age,
p01.Birthdate,
p01.COUNTRYOFBIRTH + ' - ' + COUNTRY.COUNTRYNAME asAS 'Country / Location',
PCAB.Cabin,
POS.NAME as AS Position,
P0T.TELENO as AS Email,
mob.Teleno as AS 'Mobile Phone',
p01.RANK as AS 'Current Rank',
p01.Client numorgid,
VSL.NUMORGID AS vessel_numorgid,
p01.EmploymentStartDate,
p01.EmploymentEnddate,
from dbo.pw001p01 p01 JOIN dbop01.PW001P03COSTPLACE P03 AS 'Department/Cost Place Code',
c43.[NAME] ON P01.PIN = P03.PIN AS 'Department/Cost Place'
FROM dbo.pw001p01 p01
JOIN dbo.PW001P03 AND (P03.DATETO
IS NULL or P03.DATETO >= GETDATE()) ON P01.PIN = P03.PIN
AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE())
AND P03.DATEFROM <= GETDATE()
LEFt
LEFT JOIN Pw001C12 act on
ON act.code = p03.code
JOIN 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
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 where act.OPTIONS like LIKE '%S%'
|