Try this. If you have post the sample data.
declare @m varchar(2), @y varchar(4)
set @m = '08'
set @y=2012
DECLARE @date DATETIME
SELECT @date = convert(datetime, @m + '-01' + '-' + @y)
SELECT @date=DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))
declare @Empmaster table (
Emp_Joinindate datetime,Emp_leavingdate datetime,Emp_Status varchar(2)
)
insert @Empmaster values ('04-01-2012','05-01-2012','1')
insert @Empmaster values ('04-01-2012',NULL,'1')
insert @Empmaster values ('05-01-2012',null,'1')
insert @Empmaster values ('05-01-2012','07-01-2012','1')
insert @Empmaster values ('06-01-2012',null,'1')
insert @Empmaster values ('07-01-2012',null,'1')
//Active emp
select * from @Empmaster
where Emp_Joinindate <= @date and (ISNULL(Emp_leavingdate,'12-31-9999')) >= @date
// Emp left
select * from @Empmaster
where Emp_Joinindate <= @date and (ISNULL(Emp_leavingdate,'12-31-9999')) <= @date