Skip to end of banner
Go to start of banner

Retention Rate

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 15 Next »

Summary

This analytical view shows the retention rate per company over a selected period of time.

Keywords

retention rate, employment state

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 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 CNT, 
                P.EMPLOYMENTSTARTDATE AS EMPDATE, 
                 P.EMPLOYMENTSTARTDATE AS EMPMONTH, 
               P.EMPLOYMENTSTARTDATE AS EMPQUARTER, 
                P.EMPLOYMENTSTARTDATE AS EMPYEAR,    
                P.EMPLOYMENTENDDATE AS EMPENDDATE,
                P.EMPLOYMENTENDDATE AS EMPENDMONTH,
                P.EMPLOYMENTENDDATE AS EMPENDQUARTER, 
                P.EMPLOYMENTENDDATE AS EMPENDYEAR,               
                'Empl. Start' AS REASON,
                'Employment Started' AS STATUS ,
cont.text as CONTRACTTYPE          
                 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 pw001c32 cont on cont.code=p.contracttype    
   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 CNT,     
                P.EMPLOYMENTSTARTDATE AS EMPDATE, 
                 P.EMPLOYMENTSTARTDATE AS EMPMONTH, 
               P.EMPLOYMENTSTARTDATE AS EMPQUARTER, 
                P.EMPLOYMENTSTARTDATE AS EMPYEAR,    
                P.EMPLOYMENTENDDATE AS EMPENDDATE,
                P.EMPLOYMENTENDDATE AS EMPENDMONTH,
                P.EMPLOYMENTENDDATE AS EMPENDQUARTER, 
                P.EMPLOYMENTENDDATE AS EMPENDYEAR,                              
                ISNULL(C55E.NAME, 'Not Specified') AS REASON,                                
                'Employment Ended' AS STATUS ,
cont.text as CONTRACTTYPE         
                 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     
left join pw001c32 cont on cont.code=p.contracttype
   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.

  • No labels