Description
A view that will show the crew who has Signed On from previous month
View Sample
Month today is March - it shows Signed on dates from February
Main Data Selection
Selection of the view is based on crews having current activity with a datefrom less than today and dateto is blank or greater than today (does not concern todateestimated).
There is also a condition that DateFrom should be in 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.