Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Expand
titleSQL Statement
Code Block
CREATE VIEW dbo.PW001SRV134 AS 

SELECT p01.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 'Full Name',
       p01.FIRSTNAME         AS 'First Name',
       p01.MIDDLENAME        AS 'Middle Name',
       p01.LASTNAME          AS 'Last Name',
       DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), birthdate) 'Celebration Date',
       MONTH(p01.birthdate)  AS 'Month Nr',
       DATENAME(
           mm,
           DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), p01.birthdate)
       )                        MONTH,
       CONVERT(
           INT,
           DATENAME(
               d,
               DATEADD(YY, DATEDIFF(YY, P01.BIRTHDATE, GETDATE()), BIRTHDATE)
           )
       )                     AS DATE,
       DATENAME(
           wk,
           DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), birthdate)
       )                        Week,
       DATENAME(
           weekday,
           DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), birthdate)
       )                        DAY,
       DATEDIFF(yy, p01.birthdate, GETDATE()) Age,
       p01.Birthdate,
       p01.COUNTRYOFBIRTH + ' - ' + COUNTRY.COUNTRYNAME AS 'Country / Location',
       PCAB.Cabin,
       POS.NAME              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,
       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 OR P03.DATETO >= GETDATE())
            AND P03.DATEFROM <= GETDATE()
       LEFT JOIN Pw001C12 act
            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 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  act.OPTIONS              LIKE '%S%'

...