-- Time: 9/14/2023 7:11:26 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.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 DATEADD(d, FROM PWROT_ACTIVE_SHIFT
CROSS APPLY (
SELECT TOP(DATEDIFF(DAY, DATEFROM, DATETO) + 1)
ROW_NUMBER() OVER(
day, DATEADD(day, -60, CONVERT(VARCHAR, GETDATE(), 112))) AS Onboard
ORDER BYFROM(
SELECT NULLDISTINCT
id % )
) - 1 AS Number
365 + 1 AS day
FROM sys.columns AS c1
CROSS JOIN sys.columns AS c2
) AS a
WHERE DATEFROM <= DATETO
) AS CheckDate
FROM sysobjects)AS a)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 |