SELECT P.PIN,
       P.NAME AS NAME,
       p03.DATEFROM as 'ACTIVITY DATE FROM',
       p03.DATETO as 'ACTIVITY DATE TO',
       p03.code AS ACTIVITY,
       orgpos.name AS POSITION,
       ISNULL(pos.mbr2, 0) AS 'COMPANY REQUIRED MANNING',
       CASE
           WHEN CHARINDEX ('.', dep.name ) > 0 THEN  SUBSTRING(dep.name, 5, LEN(dep.name) - 4)
           ELSE dep.name
       END AS DEPARTMENT,
       ves.name AS VESSEL,
       CONVERT(DATETIME, CONVERT(VARCHAR, CheckDate.Onboard, 112)) AS 'ONBOARD ON',
       1 AS 'CREW COUNT',
       P.CLIENT NUMORGID,
       P.EMPLOYMENTSTARTDATE,
       P.EMPLOYMENTENDDATE
FROM PW001P01 AS P
     JOIN PW001P03 AS P03
          ON P.PIN = P03.PIN
     JOIN PW001C12 AS C12
          ON P03.CODE = C12.CODE
         AND C12.OPTIONS LIKE '%S%'
     JOIN(
          SELECT DISTINCT
                 DATEADD(d, day, DATEADD(day, -60, CONVERT(VARCHAR, GETDATE(), 112))) AS Onboard
          FROM(
               SELECT DISTINCT
                      id % 365 + 1 AS day
               FROM sysobjects)AS a)AS CheckDate
          ON p03.datefrom <= CheckDate.Onboard
         AND ISNULL(p03.dateto, CheckDate.Onboard) >= CheckDate.Onboard
         AND CheckDate.Onboard <= DATEADD(day, 60, CONVERT(VARCHAR, GETDATE(), 112))
     LEFT JOIN PWORG AS orgpos
          ON dbo.ad_scanorgtree(p03.numorgid, 5) = orgpos.NUMORGID
     LEFT JOIN PWORGPOS AS pos
          ON pos.NUMORGID = orgpos.NUMORGID
     LEFT JOIN PW001C02 AS C02
          ON dbo.ad_orgPosC02Code(orgpos.orgcode) = c02.code
     LEFT JOIN PWORG AS DEP
          ON dbo.ad_scanorgtree(p03.numorgid, 4) = DEP.NUMORGID
     LEFT JOIN PWORG AS VES
          ON dbo.ad_scanorgtree(p03.numorgid, 3) = VES.NUMORGID