SQL Server 2017

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

create table t1(empid int,dateofjoin date, dateofleaving date, status varchar(20))
insert into t1(empid, dateofjoin, dateofleaving, status)
values(1,'2015-10-01','2017-09-26','De-Active'),
(2,'2018-01-01',null,'Active'),
(3,'2018-10-15',null,'Active')
3 rows affected

select empid, dateofjoin, dateofleaving, status
from t1
where DateOFJoin<='2019-12-01' and coalesce(dateofleaving,'2019-12-31')>='2019-12-31'


empid
dateofjoin
dateofleaving
status


2
2018-01-01
null
Active

3
2018-10-15
null
Active