Description

Crewlist view will show all those crew who was born after 1970 and incomebasedpensionscheme = 'Y'.

Selection

SELECT p01.PIN, p01.NAME, p01.LASTNAME, p01.FIRSTNAME, p01.MIDDLENAME, p01.PERSONALIDNO, p01.NATIONALITY, p01.SEX AS GENDER, p01.BIRTHDATE,DATEDIFF(YEAR, p01.BIRTHDATE, GETDATE()) - CASE WHEN 100 * MONTH(GETDATE()) + DAY(GETDATE()) < 100 * MONTH(p01.birthdate) + DAY(p01.birthdate)
THEN 1 ELSE 0 END AS AGE, ORG.NAME AS ORGANIZATION, p01.ADDRESS1, p01.ADDRESS2, p01.ADDRESS3, telephone.TELENO AS 'HOME PHONE',mobile.TELENO AS 'MOBILE', email.TELENO AS EMAIL, residencecountry.COUNTRYNAME AS 'ADDRESS COUNTRY', c32.TEXT AS 'CONTRACT TYPE',
p01.CONTRACTSTARTDATE AS 'CONTRACT START', p01.CONTRACTEXPIRYDATE AS 'CONTRACT END', P03.DATEFROM AS 'ACTIVITY START', ISNULL(P03.DATETO,P03.TODATEESTIMATED) AS 'ACTIVITY END', DEP.NAME AS DEPARTMENT, POS.NAME AS POSITION, pass.PASSPORTNO,
pass.PASSPORTEXPIRYDATE AS 'PASS EXPIRY', P03.RANK, VSL.NAME AS VESSEL, DEP.NUMORGID AS department_numorgid,VSL.NUMORGID AS VESSEL_NUMORGID, p01.CLIENT AS numorgid, p01.EMPLOYMENTSTARTDATE, p01.EMPLOYMENTENDDATE
FROM PW001P01 AS p01 LEFT OUTER JOIN
PWORG AS ORG ON p01.CLIENT = ORG.NUMORGID LEFT OUTER JOIN
PW001C32 AS c32 ON c32.CODE = p01.CONTRACTTYPE INNER JOIN
PW001P03 AS P03 ON p01.PIN = P03.PIN AND (P03.DATETO IS NULL OR
P03.DATETO >= GETDATE()) AND P03.DATEFROM <= GETDATE() AND P03.PLANNED <> 'Y' INNER JOIN
PW001C12 AS c12 ON c12.CODE = P03.CODE AND c12.OPTIONS LIKE '%S%' LEFT OUTER JOIN
PWROT_SHIFT_ACTIVITIES AS psa ON psa.ACTIVITIES = P03.SEQUENCENO LEFT OUTER JOIN
PWROT_ACTIVE_SHIFT AS pas ON pas.SEQUENCENO = psa.SEQUENCENO INNER JOIN
PWORG AS VSL ON VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3) LEFT OUTER JOIN
PWORG AS DEP ON DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4) LEFT OUTER JOIN
PWORG AS POS ON POS.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 5) LEFT OUTER JOIN
PWORGVESCAB AS PCAB ON PCAB.CABINID = P03.cabinid LEFT OUTER JOIN
PWCCMCHANGECREWLIST AS cclj ON cclj.NEXTACT = P03.SEQUENCENO LEFT OUTER JOIN
PWORGVESACT AS vaj ON vaj.SEQNO = cclj.SEQNO LEFT OUTER JOIN
PWPORT AS portj ON portj.PORTCODE = vaj.PLACEFROM LEFT OUTER JOIN
PWCCMCHANGECREWLIST AS ccll ON ccll.CURRENTACT = P03.SEQUENCENO LEFT OUTER JOIN
PWORGVESACT AS val ON val.SEQNO = ccll.SEQNO LEFT OUTER JOIN
PWPORT AS portl ON portl.PORTCODE = val.PLACEFROM LEFT OUTER JOIN
PW001P0T AS mobile ON mobile.PIN = p01.PIN AND mobile.TELETYPE = 3 AND NOT EXISTS
(SELECT 1 AS Expr1
FROM PW001P0T AS t
WHERE (PIN = mobile.PIN) AND (TELETYPE = mobile.TELETYPE) AND (TELEPRIORITY < mobile.TELEPRIORITY) OR
(PIN = mobile.PIN) AND (TELETYPE = mobile.TELETYPE) AND (TELEPRIORITY = mobile.TELEPRIORITY) AND (SEQUENCENO > mobile.SEQUENCENO))
LEFT OUTER JOIN
PW001P0T AS telephone ON telephone.PIN = p01.PIN AND telephone.TELETYPE = 2 AND NOT EXISTS
(SELECT 1 AS Expr1
FROM PW001P0T AS t
WHERE (PIN = telephone.PIN) AND (TELETYPE = telephone.TELETYPE) AND (TELEPRIORITY < telephone.TELEPRIORITY) OR
(PIN = telephone.PIN) AND (TELETYPE = telephone.TELETYPE) AND (TELEPRIORITY = telephone.TELEPRIORITY) AND
(SEQUENCENO > telephone.SEQUENCENO)) LEFT OUTER JOIN
PW001P0T AS email ON email.PIN = p01.PIN AND email.TELETYPE = 6 AND NOT EXISTS
(SELECT 1 AS Expr1
FROM PW001P0T AS t
WHERE (PIN = email.PIN) AND (TELETYPE = email.TELETYPE) AND (TELEPRIORITY < email.TELEPRIORITY) OR
(PIN = email.PIN) AND (TELETYPE = email.TELETYPE) AND (TELEPRIORITY = email.TELEPRIORITY) AND (SEQUENCENO > email.SEQUENCENO))
LEFT OUTER JOIN
PWCOUNTRY AS residencecountry ON residencecountry.COUNTRYCODE = p01.ADDRESS_COUNTRY LEFT OUTER JOIN
PW001P1P AS pass ON pass.PIN = p01.PIN AND pass.CODE = 'PP' LEFT OUTER JOIN
PW001C02 AS C02 ON C02.CODE = dbo.ad_orgPosC02Code(POS.ORGCODE)
WHERE (p01.PIN IN
(SELECT PIN
FROM PW001P0Y
WHERE (IncomebasedPensionScheme = 'Y'))) AND (p01.BIRTHDATE >= CONVERT(DATETIME, '1970-01-01 00:00:00', 102))Field Specification

Other