...

Description

A view that will show the crew who has Signed On from previous monthshows 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., The current month is on March, so the view shows the crew who signs 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

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
PIN	Personal Details > Personal > Personal Identification Number
NUMORGID	Personal Details > Employment > Organizations NUMORGID
EMPLOYMENTSTARTDATE	Personal Details > Employment > Employment Start Date
EMPLOYMENTENDDATE	Personal Details > Employment > Employment End Date
EMPLOYMENTSTATE	Personal Details > Employment > Employment Start Date
FIRSTNAME	Personal Details > Personal > Firstname
LASTNAME	Personal Details > Personal > Lastname
STARTDATE	Activity Datagroup > Current Activity > Start Date
ENDDATE	Activity Datagroup > Current Activity > Date From or To Date Estimated

Start Page

N/A