question

Sam-6921 avatar image
0 Votes"
Sam-6921 asked Viorel-1 edited

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

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How is the final number of contracts defined?

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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:
79164-image.png


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.




image.png (2.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.