SELECT DISTINCT 
 ltrim(rtrim(COLELE01.NOM)) as NOM 
, ltrim(rtrim(COLELE01.PRENOM)) as PRENOM 
, CONCAT('9', COLELE01.NUMERODA) as NUMERODA  
, format(cast(COLELE01.DATENAISSANCE  as date),'yyyyMMdd') as DATENAISSANCE 
, COLELE01.CODEPERM 
, CONCAT(CONCAT('9', COLELE01.NUMERODA),'@cegepadistance.ca') as COURRIEL_INSTITUTIONNEL
, ltrim(rtrim(COBNIC02.ADRELECTRO))  as COURRIELPERSONNEL 
, case  CADINS01.SITUATIONETUDE when 'CS' then 'COMMANDITE' else CADINS01.SITUATIONETUDE end   as  SITUATIONETUDE
, CADINS01.DATEINS 
FROM CADINS01   
INNER JOIN COLELE01 ON COLELE01.NUMERODA = CADINS01.NUMERODA   
INNER JOIN CADCRS01 ON CADINS01.CODEMEQ = CADCRS01.CODEMEQ AND CADINS01.CODEOPTION = CADCRS01.CODEOPTION  AND CADINS01.VERSIONCOURS = CADCRS01.VERSIONCOURS   
INNER JOIN COBNIC02 ON COBNIC02.NIC = COLELE01.NIC  
INNER JOIN COLMEQ01 ON CADINS01.CODEMEQ =  COLMEQ01.CODEMEQ 
WHERE (CADINS01.STATUTINS IN ('1', '6', '9','M'))   
AND (CADINS01.SITUATIONETUDE = 'SO')   
AND (CADCRS01.TYPECOMPFORM <> 'SO')  
AND (COLMEQ01.TYPECODEMEQ <> 2) 
 AND cast(CADINS01.DATEINS as date ) >= DATEADD(day, -1,cast (getdate() as date))  
UNION  
SELECT DISTINCT 
 ltrim(rtrim(COLELE01.NOM)) as NOM 
, ltrim(rtrim(COLELE01.PRENOM)) as PRENOM 
, CONCAT('9', COLELE01.NUMERODA) as NUMERODA  
, format(cast(COLELE01.DATENAISSANCE  as date),'yyyyMMdd') as DATENAISSANCE 
, COLELE01.CODEPERM 
, CONCAT(CONCAT('9', COLELE01.NUMERODA),'@cegepadistance.ca') as COURRIEL_INSTITUTIONNEL
, ltrim(rtrim(COBNIC02.ADRELECTRO)) as COURRIELPERSONNEL 
, case  CADINS01.SITUATIONETUDE when 'CS' then 'COMMANDITE' else CADINS01.SITUATIONETUDE end   as  SITUATIONETUDE
, CADINS01.DATEINS 
FROM CADINS01  
INNER JOIN COLELE01 ON COLELE01.NUMERODA = CADINS01.NUMERODA  
INNER JOIN CADCRS01 ON CADINS01.CODEMEQ = CADCRS01.CODEMEQ  AND CADINS01.CODEOPTION = CADCRS01.CODEOPTION  AND CADINS01.VERSIONCOURS = CADCRS01.VERSIONCOURS  
INNER JOIN COBNIC02 ON COBNIC02.NIC = COLELE01.NIC  
INNER JOIN COLMEQ01 ON CADINS01.CODEMEQ =  COLMEQ01.CODEMEQ 
WHERE (CADINS01.STATUTINS IN ('1', '6', '9','M'))  
AND CADINS01.SITUATIONETUDE = 'CS' 
AND (CADCRS01.TYPECOMPFORM <> 'SO') 
AND (COLMEQ01.TYPECODEMEQ <> 2) 
AND ((CADCRS01.CODEMEQ = '420105FD' and CADCRS01.CODEOPTION ='60' and CADCRS01.VERSIONCOURS = 5) 
	or (CADCRS01.CODEMEQ = '410303FD'  and CADCRS01.CODEOPTION ='60' and CADCRS01.VERSIONCOURS = 2) 
	or (CADCRS01.CODEMEQ = '410654FD' and CADCRS01.CODEOPTION ='60' and CADCRS01.VERSIONCOURS = 1)  
	) 
 AND cast(CADINS01.DATEINS as date ) >= DATEADD(day, -1,cast (getdate() as date))  
UNION  
SELECT DISTINCT 
 ltrim(rtrim(COLELE01.NOM)) as NOM 
, ltrim(rtrim(COLELE01.PRENOM)) as PRENOM 
, CONCAT('9', COLELE01.NUMERODA) as NUMERODA  
, format(cast(COLELE01.DATENAISSANCE  as date),'yyyyMMdd') as DATENAISSANCE 
, COLELE01.CODEPERM 
, CONCAT(CONCAT('9', COLELE01.NUMERODA),'@cegepadistance.ca') as COURRIEL_INSTITUTIONNEL
, ltrim(rtrim(COBNIC02.ADRELECTRO))  as COURRIELPERSONNEL 
, 'EUL' as SITUATIONETUDE 
, COLELE12.EULEDATEEPREVUE  as DATEINS
 FROM COLELE01 
 inner join COLELE12 on COLELE12.NUMERODA = COLELE01.NUMERODA 
 inner join COBNIC02 on  COBNIC02.NIC = COLELE01.NIC 
 where COLELE12.EULEINSACTIVE = 1 
 and COLELE12.EULEDATEEPREVUE >=GETDATE()  