-- 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 Onboard    FROM   PWROT_ACTIVE_SHIFT
                       CROSS APPLY (
                    SELECT TOP(DATEDIFF(DAY, DATEFROM, DATETO) + 1)
                           ROW_NUMBER() OVER(
                               FROM(ORDER BY(
                                   SELECT DISTINCT NULL
                               )
                         id % 365) +- 1        AS day Number
                    FROM sysobjects)AS a)   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) = VES.NUMORGID
  VES.NUMORGID = ou.VesselID
--to generate only for the current year enable line below
--where Year(CheckDate.Onboard) = Year(Getdate())