-- Time: 9/14/2023 7:16:31 PM
-- IMPLADC-222
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.mbr1, 0) AS 'MINIMUM SAFE MANNING',
dep.[NAME] AS DEPARTMENT,
ves.name AS VESSEL,
--CONVERT(DATETIME, CONVERT(VARCHAR, CheckDate.Onboard, 112)) AS 'ONBOARD ON',
CheckDate.Onboard 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 (
SELECT TOP(DATEDIFF(DAY, DATEFROM, DATETO) + 1)
ROW_NUMBER() OVER(
ORDER BY(
SELECT NULL
)
) - 1 AS Number
FROM 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(DAY, 60, CONVERT(VARCHAR, GETDATE(), 112))
LEFT JOIN (
SELECT NUMORGID,
dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,
dbo.ad_scanorgtree(NUMORGID, 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 DEP.NUMORGID = ou.DepID
LEFT JOIN PWORG AS VES
ON VES.NUMORGID = ou.VesselID
--to generate only for the current year enable line below
--where Year(CheckDate.Onboard) = Year(Getdate()) | 