Page Comparison

CREATE VIEW dbo.CrewData_API AS

SELECT P01.PIN,
       P01.FIRSTNAME,
       P01.LASTNAME,
       P01.SEX                            GENDER,
       C02.NAME                           RANK,
       P01.NATIONALITY,
       P01.BIRTHDATE,
       P01.PLACEOFBIRTH,
       P1P.PASSPORTNO                     PASSPORTNO,
       P1P.PASSPORTEXPIRYDATE             PASSPORTEXPIRY,
       P1P.PASSPORTISSUEDATE,
       P1P.PASSPORTISSUECOUNTRY,
       P0T.TELENO                         EMAIL,
       MOBILE.TELENO                      MOBILE,
       CASE 
            WHEN p03Cur.SEQUENCENO IS NOT p03.PLANNED <> 'Y' AND p03.DATETO IS NULL THEN 'Y'
            ELSE 'N'
       END                             AS ONBOARDSTATUSONBOARD_STATUS,
  	     p03Curp03.SEQUENCENO		ONB_ACTIVITY_SEQUENCENO            --Status signed on the vessel or signed off - TBAONB_ACTIVITY_SEQUENCENO
FROM   PW001P01 P01
       LEFT JOIN PW001P03 p03Cur
            ON  P01.PIN = p03Cur.PIN
            AND p03Cur.DATETO IS NULL
            AND ISNULL(p03Cur.PLANNED, 'N') <> 'Y'
            AND EXISTS(
                    SELECT 1
                    FROM   PW001C12 t1
                    WHERE  t1.CODE = p03Cur.CODE
                           AND t1.OPTIONS LIKE '%S%'
                )
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT pl.PIN,
                                  pl.SEQUENCENO,
                                  pl.CODE,
                                  ROW_NUMBER() OVER(PARTITION BY pl.PIN ORDER BY pl.DATEFROM ASC)Nr
                           FROM   pw001p03 pl
                           WHERE  pl.DATETO IS NULL
                                  AND ISNULL(pl.PLANNED, 'N') = 'Y'
                                  AND EXISTS(
                                          SELECT 1
                                          FROM   PW001C12 t2
                                          WHERE  t2.CODE = pl.CODE
                                                 AND t2.OPTIONS LIKE '%S%'
                                      )
                       )tpl
                WHERE  tpl.Nr = 1
            )p03Planned
            ON  p01.pin = p03Planned.PIN
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT lh.PIN,
                                  lh.SEQUENCENO,
                                  lh.CODE,
                                  ROW_NUMBER() OVER(PARTITION BY lh.PIN ORDER BY lh.DATEFROM DESC)Nr
                           FROM   pw001p03 lh
                           WHERE  lh.DATETO IS NOT NULL
                                  AND ISNULL(lh.PLANNED, 'N') <> 'Y'
                                  AND EXISTS(
                                          SELECT 1
                                          FROM   PW001C12 t3
                                          WHERE  t3.CODE = lh.CODE
                                                 AND t3.OPTIONS LIKE '%S%'
                                      )
                       )tlh
                WHERE  tlh.Nr = 1
            )p03LatestHistorical
            ON  p01.pin = p03LatestHistorical.PIN
       JOIN PW001P03 P03
            ON  p03.SEQUENCENO = ISNULL(ISNULL(ISNULL(p03Cur.SEQUENCENO, p03Planned.SEQUENCENO), p03LatestHistorical.SEQUENCENO),p03Cur.SEQUENCENO)
       LEFT JOIN PW001C02 C02
            ON  C02.CODE = P01.RANK
       LEFT JOIN PW001P1P P1P
            ON  P01.PIN = P1P.PIN
            AND p1p.code = 'PP'
       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 MOBILE
            ON  (
                    (P01.PIN = MOBILE.PIN)
                    AND (MOBILE.TELETYPE = 3)
                    AND (NOT MOBILE.TELEPRIORITY IS NULL)
                    AND NOT EXISTS
                        (
                            SELECT SEQUENCENO
                            FROM   PW001P0T MOBILE2
                            WHERE  (MOBILE.PIN = MOBILE2.PIN)
                                   AND (MOBILE2.TELETYPE = 3)
                                   AND (
                                           (MOBILE2.TELEPRIORITY < MOBILE.TELEPRIORITY)
                                           OR (
                                                  (MOBILE2.TELEPRIORITY = MOBILE.TELEPRIORITY)
                                                  AND (MOBILE2.SEQUENCENO < MOBILE.SEQUENCENO)
                                              )
                                       )
                        )
                )

--WHERE P01.EMPLOYMENTENDDATE IS NULL OR P01.EMPLOYMENTENDDATE > GETDATE() --when enabled - only for active crew

GO