Skip to end of banner
Go to start of banner

New Joiners, Previous month

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Version History

« Previous Version 3 Next »

Summary

A view that will show the crew who has Signed On from previous month

Keywords

New Joiners

Category

Crew List View

Description

A view that shows all the employed/active crew members who Signed On in the previous month on a particular vessel.

View Sample

e.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 date from less than today and a date to is blank or greater than today (does not concern EED).
There is also a condition that DateFrom should be in the previous month. (Signed On from previous month).

SQL statement

 Click here to expand...
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)

Column

Description/ Location in APM

Start Page

N/A

  • No labels