Get Start and End Dates per Employment Period

Transact-SQL
1

Hi All, Need to get Start and End Date per eployment period, meaning an employee can be hired and rehired.

I attached a sample data and desired output.

sample_data
sample_data859×238 6.27 KB

Thanks

2 
drop table #SampleData 

create table #SampleData
(
EmployeeID int , StartDate date null, EndDate date , EmpType varchar(100) , EmpStartType varchar(100) ,HireReason varchar(100) , LeavingReason varchar(100)
)

insert into #SampleData Values 
   (100001,'4/2/2019' , '6/9/2020' ,'Fulltime','ExternalRecruit','','') 
,  (100001,'7/9/2020' , '8/11/2020','Parttime','InternalRecruit','','')  
,  (100001,'9/11/2020', '8/1/2021' ,'Fulltime','InternalRecruit','','End Of Contract')  
,  (100001,'1/2/2021',    null     ,'Fulltime','ExternalRecruit','Re-Hire','')  

select * from #SampleData where LeavingReason = 'End Of Contract'
union all 
select * from #SampleData where HireReason = 'Re-Hire'