Description
This analytical view shows the retention rate per company over a selected period of time.
The selection is based on the employment state under the Personal Details > Employment tab.
Based on the employment state (employed or terminated) the view identifies current status of the employee.
All the terminated people are split based on their termination reason: resigned, retired, deceased, dismissed.
The view allows the user to choose the time period for the selection by year, quarter, month, day.
Setup and Configuration
Selection
SQL Code
SELECT P.PIN,
P.NAME,
P.EMPLOYMENTSTARTDATE,
P.EMPLOYMENTENDDATE,
P.NATIONALITY,
P.CLIENT NUMORGID,
ISNULL(O.NAME, O2.NAME) AS ORGANIZATION,
C02.NAME AS RANK,
P.SEX,
P.PIN COUNT,
P.EMPLOYMENTSTARTDATE AS YEAR,
P.EMPLOYMENTSTARTDATE AS QUARTER,
P.EMPLOYMENTSTARTDATE AS MONTH,
'Employment Start' AS REASON,
'Active' AS STATUS
FROM PW001P01 P
LEFT JOIN PW001P0P P0P ON P.PIN=P0P.PIN AND PNUMBER='A'
LEFT JOIN PWORG O ON O.NUMORGID=dbo.ad_ScanOrgTree(P.CLIENT,3)
LEFT JOIN PWORG O2 ON O2.NUMORGID=dbo.ad_ScanOrgTree(P.CLIENT,2)
LEFT JOIN PW001C02 C02 ON C02.CODE=P0P.POSITIONID
WHERE (NOT P.EMPLOYMENTSTARTDATE IS NULL) AND (NOT C02.NAME IS NULL)
UNION
SELECT P.PIN,
P.NAME,
P.EMPLOYMENTSTARTDATE,
P.EMPLOYMENTENDDATE,
P.NATIONALITY,
P.CLIENT NUMORGID,
ISNULL(O.NAME, O2.NAME) AS ORGANIZATION,
C02.NAME AS RANK,
P.SEX,
P.PIN COUNT,
P.EMPLOYMENTENDDATE AS YEAR,
P.EMPLOYMENTENDDATE AS QUARTER,
P.EMPLOYMENTENDDATE AS MONTH,
ISNULL(C55E.NAME, 'Not Specified') AS REASON,
'Inactive' AS STATUS
FROM PW001P01 P
LEFT JOIN PW001P0P P0P ON P.PIN=P0P.PIN AND PNUMBER='A'
LEFT JOIN PWORG O ON O.NUMORGID=dbo.ad_ScanOrgTree(P.CLIENT,3)
LEFT JOIN PWORG O2 ON O2.NUMORGID=dbo.ad_ScanOrgTree(P.CLIENT,2)
LEFT JOIN PW001C02 C02 ON C02.CODE=P0P.POSITIONID
LEFT JOIN PW001C55 C55E ON C55E.CODE=P.STOPREASON
WHERE (NOT P.EMPLOYMENTENDDATE IS NULL) AND (NOT C02.NAME IS NULL)
Fields Definition
View Configuration
This is the example of the “Retention rate” view setup for the whole year split by the organization, sex, PIN, and Name.
Per Organization
By Position
