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 CNT, 
       COUNT, 
       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 StartedActive' 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,     
   COUNT,     
       P.EMPLOYMENTSTARTDATE AS EMPDATE, 
                 P.EMPLOYMENTSTARTDATE AS EMPMONTH, 
               P.EMPLOYMENTSTARTDATE AS EMPQUARTER, 
                P.EMPLOYMENTSTARTDATE AS EMPYEAR,    
                P.EMPLOYMENTENDDATE AS EMPENDDATEYEAR, 
	               P.EMPLOYMENTENDDATE AS EMPENDMONTHQUARTER,
             	   P.EMPLOYMENTENDDATE AS EMPENDQUARTERMONTH, 
                P.EMPLOYMENTENDDATE AS EMPENDYEAR,      
                       
	                ISNULL(C55E.NAME, 'Not Specified') AS REASON,                                
             	   'Employment EndedInactive' 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)