...
A view that shows all the employed/active crew members who Signed On in the previous month on a particular vessel.
View Sample
Month today is March - it shows Signed on dates from Februarye.g here the current month is March, so the view shows the crew signing on in February.
...
Main Data Selection
Selection of the view is based on crews having current activity with a datefrom date from less than today and dateto a date to is blank or greater than today (does not concern todateestimatedEED).
There is also a condition that DateFrom should be in the previous month. (Signed On from previous month).
SQL statement
| Expand |
|---|
| Code Block |
|---|
| SELECT p.PIN,
p.CLIENT NUMORGID,
p.EMPLOYMENTSTARTDATE,
p.EMPLOYMENTENDDATE,
CASE
WHEN p.EMPLOYMENTSTARTDATE IS NOT NULL AND CONVERT(DATE, p.EMPLOYMENTSTARTDATE) <= CONVERT(DATE, GETDATE())
AND (
p.EMPLOYMENTENDDATE IS NULL
OR CONVERT(DATE, p.EMPLOYMENTENDDATE) > CONVERT(DATE, GETDATE())
) THEN 'Employed'
WHEN p.EMPLOYMENTENDDATE IS NOT NULL AND CONVERT(DATE, p.EMPLOYMENTENDDATE) <= CONVERT(DATE, GETDATE()) THEN
'Terminated'
ELSE ''
END EmploymentState,
p.FIRSTNAME,
p.LASTNAME,
(
SELECT v.[NAME]
FROM PWORG v
WHERE v.NUMORGID = dbo.ad_scanorgtree(a.NUMORGID, 3)
) Vessel,
CONVERT(DATE, a.DATEFROM) StartDate,
CONVERT(DATE, ISNULL(a.DATETO, a.TODATEESTIMATED)) EndDate
FROM PW001P01 p
JOIN PW001P03 a
ON a.CODE IN (SELECT CODE
FROM PW001C12
WHERE OPTIONS LIKE '%S%')
AND a.PIN = p.PIN
AND CONVERT(DATE, a.DATEFROM) < CONVERT(DATE, GETDATE())
AND ISNULL(a.DATETO, GETDATE()) >= GETDATE()
AND a.PLANNED != 'Y'
AND CONVERT(DATE, a.DATEFROM) BETWEEN DATEADD(MONTH,-1, CONVERT(DATE, LEFT(CONVERT(VARCHAR, GETDATE(), 112), 6) + '01'))
AND DATEADD(DAY,-1,CONVERT(DATE, LEFT(CONVERT(VARCHAR, GETDATE(), 112), 6) + '01')) |
|
Columns Specification
Mandatory fields for Crew List View (PIN, NUMORGID usually CLIENT from Personal Details, EMPLOYMENTSTARTDATE, EMPLOYMENTENDDATE)
FIRSTNAME
LASTNAME
VESSELNAME
EMPLOYMENTSTATE (EMPLOYED/TERMINATED)
STARTDATE(Activity Date From)
ENDDATE(Activity DateTo/TodateEstimated)
Start Page
Link to the documentation on the related Start Page Tile.
...
Column | Description/ Location in APM |
|---|
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
Start Page
N/A