How to query current employment status along with number of past contract

Question

Hi,

I have a sample table below:

ID NAME CONTRACT_START_DATE CONTRACT_END_DATE
1234 ABC 2019-01-01 2019-06-30
1234 ABC 2019-06-15 2019-12-31
1222 DEF 2019-01-01
1234 ABC 2020-01-03

I have been assigned to retreive list of staff who has been rehire within 7 days from his/her last contract date, along with number of contract awarded to satff in the past.

The output query will look like

ID Name Contract_Start_Date Number of last awarded contract
1234 ABC 2020-01-03 2

Best regards
SAM

Answer 1

Please try:

    declare @test table(ID int,NAME char(15),CONTRACT_START_DATE  date,CONTRACT_END_DATE date)  
      
    insert into @test values(1234,'ABC','2019-01-01','2019-06-30'),  
    (1234,'ABC','2019-06-15','2019-12-31'),  
    (1222,'DEF','2019-01-01',null),  
    (1234,'ABC','2020-01-03',null)  
      
    ;with cte  
    as(select *,row_number() over(partition by ID order by CONTRACT_START_DATE)rr from @test t1)  
    ,cte2 as(select c1.*,c2.ID ID2,c2.NAME NAME2,c2.CONTRACT_START_DATE CONTRACT_START_DATE2,  
    c2.CONTRACT_END_DATE CONTRACT_END_DATE2,c2.rr rr2 from cte c1  
    left join cte c2 on c1.rr=c2.rr-1 and c1.ID=c2.ID)  
      
    select distinct ID,Name,min(Contract_Start_Date) over(partition by ID order by ID) Contract_Start_Date,  
    count(Contract_Start_Date ) over(partition by ID order by ID) num from cte2  
    where ID in(select ID from cte2  
    where datediff(day,CONTRACT_END_DATE2,CONTRACT_START_DATE)<7)  

Output:

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Check this attempts too:

select 
    ID, 
    Name, 
    (select CONTRACT_START_DATE from MyTable where ID = t.ID and CONTRACT_END_DATE is null) as Contract_Start_Date, 
    (select count(CONTRACT_END_DATE) from MyTable where ID = t.ID) as [Number of last awarded contract]
from MyTable t
where exists (select * from MyTable where ID = t.ID and datediff(day, t.CONTRACT_END_DATE, CONTRACT_START_DATE) <= 7)
group by ID, Name