1 Description | 2 View Sample | 3 Main Data Selection | 4 SQL statement | 5 Rows/ Columns Specification

Summary	The view compares the actual number of crew onboard with the number of each Position defined in Organization > Position > Company Required Manning in the date range 2 months before and 2 months after today’s date.
Keywords	Manning, Onboard Crew
Category	Analytical View

Description

The view compares the actual number of crew onboard with the number of each Position defined in Organization > Position > Company Required Manning:

View Sample

Main Data Selection

The view checks all the crew that have Onboard (sea-service) Activity on the date within the date range defined in the script: in the date range 2 months before and 2 months after today’s date.

SQL statement

 SELECT '1' PIN, '1'  NUMORGID, '1' EMPLOYMENTSTARTDATE,  '1' EMPLOYMENTENDDATE, 

	Vessel,
	Department,
	Position, 
	ISNULL(CompanyRequiredManning,0) AS CompanyRequiredManning,

	ISNULL(COUNT(PIN),0) AS TotalCrewOnboard,
	CASE 
		WHEN (CompanyRequiredManning - COUNT(PIN)

 ) > 0 THEN COUNT(PIN)

 - CompanyRequiredManning ELSE 0
	END AS UnderManning,
	CASE
		WHEN (COUNT(PIN)

 - CompanyRequiredManning ) > 0 THEN COUNT(PIN)

 - CompanyRequiredManning ELSE 0
	END AS OverManning,
	DateSelection
FROM(
		SELECT p.pin,
			   orgpos.name AS Position,
			   ISNULL(pos.mbr2, 0) AS CompanyRequiredManning,
			   CASE
				   WHEN c02.options LIKE '%F%' THEN 'Officer'
				   ELSE 'Rating'
			   END AS PosType,
			   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 DateSelection,
			   P.CLIENT NUMORGID,
			   P.EMPLOYMENTSTARTDATE,
			   P.EMPLOYMENTENDDATE
		FROM PWORG VES
		LEFT JOIN PW001P03 P03
			ON dbo.ad_scanorgtree(p03.numorgid, 3) = VES.NUMORGID
			AND P03.CODE IN (SELECT CODE FROM PW001C12 WHERE OPTIONS LIKE '%S%')
		LEFT JOIN PW001P01 P
			ON P.PIN = P03.PIN
		LEFT JOIN(
			SELECT DISTINCT
				DATEADD(d, day, DATEADD(day, -60, CONVERT(VARCHAR, GETDATE(), 112))) AS Onboard
			FROM(
				SELECT DISTINCT
					id % 365 + 1 AS day
				FROM sysobjects)AS a)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 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 (
				SELECT NUMORGID,
						dbo.ad_scanorgtree(NUMORGID, 4) AS DeptID
				FROM   pworg
				) ou1
			ON  ou1.NUMORGID = p03.NUMORGID
		LEFT JOIN pworg dep
			ON  dep.NUMORGID = ou1.DeptID
		LEFT JOIN PWSAILINGPLAN AS SAIL
			ON checkdate.onboard >= sail.datefrom
			AND checkdate.onboard <= sail.dateto
			AND sail.numorgid = ves.numorgid
		WHERE VES.ORGTYPE = 3 AND ISNULL(VES.PASSIVE, '') <> 'T' 
		and checkdate.Onboard is not null
	) maindata
GROUP BY
	VESSEL,
	DEPARTMENT,
	POSITION,
	CompanyRequiredManning,
	DATESELECTION

Rows/ Columns Specification

Row/Column	Description/ Location in APM

Row/Column	Description/ Location in APM
PIN	PIN (should be hidden)
NUMORGID	Vessel Organization ID (should be hidden)
EmploymentStartDate	Employment Start Date (can be hidden)
EmploymentEndDate	Employment End Date (can be hidden)
Vessel	Onboard Activity Vessel name
Department	Onboard Activity Department name
Position	Onboard Activity Position
DateSelection	Date inside the date range defined in the script: previous 60 days starding from today
CompanyRequiredManning	The number of Position according to Company requirements: Organization > Position > Company Required Manning
TotalCrewOnboard	Total number of the crew that have Onboard Activities on the specific date
OverManning	Difference between Company Required Manning (CompanyRequiredManning) and actual number of Crew Onbaard (TotalCrewOnboard) - if positive
UnderManning	Difference between Company Required Manning (CompanyRequiredManning) and actual number of Crew Onbaard (TotalCrewOnboard) - if negative

Reports, Views and Similar

Crew Onboard vs Company Required Manning v.2 (previous and next 2 months)

Description

View Sample

Main Data Selection

SQL statement

Rows/ Columns Specification