-- Time: 9/14/2023 7:16:31 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.mbr1, 0)  AS 'MINIMUM SAFE MANNING',
       dep.[NAME] CASE            WHEN CHARINDEX ('.', dep.name ) > 0 THEN  SUBSTRING(dep.name, 5, LEN(dep.name) - 4)AS DEPARTMENT,
       ves.name             AS VESSEL,
 ELSE dep.name     --CONVERT(DATETIME, CONVERT(VARCHAR,  ENDCheckDate.Onboard, 112)) AS 'ONBOARD DEPARTMENTON',
       vesCheckDate.nameOnboard AS VESSEL,'ONBOARD ON',
       1     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
                DATEADD(d, day, DATEADD(day, -60, CONVERT(VARCHAR, GETDATE(), 112))) AS OnboardFROM   PWROT_ACTIVE_SHIFT
                       CROSS APPLY (
                    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 (
                SELECT NUMORGID,
                       dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,
                      ON dbo.ad_scanorgtree(p03.numorgid, 5) = orgpos.NUMORGIDNUMORGID, 4) AS DepID
                FROM   pworg
            ) ou
            ON  ou.NUMORGID = p03.NUMORGID
       LEFT JOIN PWORG      AS orgpos
            ON  orgpos.NUMORGID = p03.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 = DEPou.NUMORGIDDepID
       LEFT JOIN PWORG      AS VES
          ON dbo.ad_scanorgtree(p03.numorgid, 3) = ON  VES.NUMORGID = ou.VesselID