-- Time: 9/14/2023 7:11:26 PM
 -- IMPLADC-222
SELECT P.PIN,
       P.NAME               AS NAME,
       p03.DATEFROM as         AS 'ACTIVITY DATE FROM',
       p03.DATETO as           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(DAY, Number, DATEFROM) AS Onboard
                FROM   PWROT_ACTIVE_SHIFT
                       CROSS APPLY DATEADD(d, day, DATEADD(day, -60, CONVERT(VARCHAR, GETDATE(), 112))) AS Onboard(
                    SELECT TOP(DATEDIFF(DAY, DATEFROM, DATETO) + 1)
                           ROW_NUMBER() OVER(
                               ORDER FROM(BY(
                                   SELECT DISTINCT NULL
                               )
                         id % 365) +- 1        AS Number
    day                FROM sysobjects)AS a)AS CheckDate   sys.columns  AS c1
                           CROSS JOIN sys.columns AS c2
                ) AS a
                WHERE  DATEFROM <= DATETO
            )               AS CheckDate
            ON  p03.datefrom <= CheckDate.Onboard
            AND ISNULL(p03.dateto, CheckDate.Onboard) >= CheckDate.Onboard
            AND CheckDate.Onboard <= DATEADD(dayDAY, 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
where Year(CheckDate.Onboard) = Year(Getdate())