Page Properties

Summary	This analytical view shows the retention rate per company over a selected period of time.
Keywords	Retention Rate, Employment Stateretention 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 the Personal Details > Employment tab.

...

Expand

title	SQL Code

Code Block

SELECT P.PIN, 
P.NAME,	  P.EMPLOYMENTSTARTDATE, P.EMPLOYMENTENDDATENAME,  
	   P.EMPLOYMENTSTARTDATE, 
       	   P.NATIONALITYEMPLOYMENTENDDATE, 
       P.NATIONALITY, 
	       P.CLIENT NUMORGID, 
	                ISNULL(O.NAME, O2.NAME) AS ORGANIZATION, 
    	            C02.NAME AS RANK, 
	                P.SexSEX,
       	         P.PIN CNTCOUNT, 
                P.EMPLOYMENTSTARTDATE AS EMPDATEYEAR, 
	                 P.EMPLOYMENTSTARTDATE AS EMPMONTHQUARTER,
	                P.EMPLOYMENTSTARTDATE AS EMPQUARTERMONTH,        
         P.EMPLOYMENTSTARTDATE AS EMPYEAR,    
                P.EMPLOYMENTENDDATE AS EMPENDDATE,
                P.EMPLOYMENTENDDATE AS EMPENDMONTH,
                P.EMPLOYMENTENDDATE AS EMPENDQUARTER, 
                P.EMPLOYMENTENDDATE AS EMPENDYEAR,               
                'Empl.	   'Employment Start' AS REASON,
    	            'Employment Started'Active' 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.EMPLOYMENTENDDATENAME, 
	   P.EMPLOYMENTSTARTDATE, 
	   P.EMPLOYMENTENDDATE,   
	      P.NATIONALITY, 
  	              P.CLIENT NUMORGID, 
	                ISNULL(O.NAME, O2.NAME) AS ORGANIZATION, 
 	               C02.NAME AS RANK,
     	           P.SexSEX,
         	       P.PIN CNT,     
                P.EMPLOYMENTSTARTDATE AS EMPDATE, 
                 P.EMPLOYMENTSTARTDATE AS EMPMONTH, 
               P.EMPLOYMENTSTARTDATE AS EMPQUARTER, 
                P.EMPLOYMENTSTARTDATE AS EMPYEARCOUNT,     
               P.EMPLOYMENTENDDATE AS EMPENDDATEYEAR, 
            	   P.EMPLOYMENTENDDATE AS EMPENDMONTHQUARTER,
            	    P.EMPLOYMENTENDDATE AS EMPENDQUARTERMONTH,                  P.EMPLOYMENTENDDATE AS EMPENDYEAR,  
                           
             	   ISNULL(C55E.NAME, 'Not Specified') AS REASON,                                
       	         'Employment Ended'Inactive' 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

...

This is the example of the “Retention rate” view setup for the whole year split by the organization, sex, PIN and Name.

...

Configuration

There are different variations on how you can setup this view. Here are few examples that you can select from:

Per Organization

Version 1

...

Version 2

...

Per Position