SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TempA
(
	emp_no nvarchar(20),
	SD nvarchar(10),
	ED nvarchar(10)
);

insert into TempA
values
('A001','2019/12/23','2020/01/03'),
('A001','2020/05/19','2020/05/25'),
('B002','2020/03/01','2020/03/10'),
('B002','2020/04/05','2020/04/15');

select *
from TempA;


emp_no
SD
ED


A001
2019/12/23
2020/01/03

A001
2020/05/19
2020/05/25

B002
2020/03/01
2020/03/10

B002
2020/04/05
2020/04/15


CREATE TABLE TableM 
(
	emp_no nvarchar(20),
	Date nvarchar(10),
	Val int
);

insert into TableM
values
('A001','2020/05/18',100),
('A001','2020/05/23',200),
('A001','2020/05/24',200),
('B002','2020/02/20',300),
('B002','2020/03/05',300),
('B002','2020/04/05',400);

select *
from TableM;


emp_no
Date
Val


A001
2020/05/18
100

A001
2020/05/23
200

A001
2020/05/24
200

B002
2020/02/20
300

B002
2020/03/05
300

B002
2020/04/05
400


select TableM.emp_no,TableM.Date,TableM.Val,
sum(case when TableM.Date>=TempB.SD and TableM.Date<=TempB.ED then 1 else 0 end) as CHK
from TableM
left join (select emp_no,SD,ED from TempA) as TempB on TempB.emp_no=TableM.emp_no 
group by TableM.emp_no,TableM.Date,TableM.Val


emp_no
Date
Val
CHK


A001
2020/05/18
100
0

A001
2020/05/23
200
1

A001
2020/05/24
200
1

B002
2020/02/20
300
0

B002
2020/03/05
300
1

B002
2020/04/05
400
1

emp_no	SD	ED
A001	2019/12/23	2020/01/03
A001	2020/05/19	2020/05/25
B002	2020/03/01	2020/03/10
B002	2020/04/05	2020/04/15

emp_no	Date	Val
A001	2020/05/18	100
A001	2020/05/23	200
A001	2020/05/24	200
B002	2020/02/20	300
B002	2020/03/05	300
B002	2020/04/05	400