Hi

Declare @tempTable table (id int, name varchar(50), status varchar(50), strtdate datetime, enddate datetime)

insert into @tempTable values (   1,     'A',   'CLSRM',      '2-Feb-14',          '5-Feb-14'    )

insert into @tempTable values (   2,     'B',   'CLSRM',      '3-Feb-14',          '6-Feb-14'    )

insert into @tempTable values (   3,     'C',   'CLSRM',      '4-Feb-14',          '7-Feb-14'    )

insert into @tempTable values (   4,     'D',   'CLSRM',        NULL,               NULL         )

insert into @tempTable values (   5,     'E',   'CLSRM',      '6-Feb-14',          '9-Feb-14'    )

insert into @tempTable values (   6,     'F',   'ELRN',         NULL,               NULL         )

insert into @tempTable values (   7,     'G',   'ELRN',         NULL,               NULL         )

insert into @tempTable values (   8,     'H',   'CLSRM',      '9-Feb-14',          '12-Feb-14'   )

insert into @tempTable values (   9,     'I',   'CLSRM',      '10-Feb-14',         '13-Feb-14'   )

insert into @tempTable values (   10,    'J',   'CLSRM',      '11-Feb-14',         '14-Feb-14'   )

--Select * from @tempTable

Here you can see few NULLs for strtdate and enddate. I want to get the result in such a way that, the record where status = 'CLSRM' and strtdate is NULL or enddate is NULL should be filtered out. If status is ELRN or anything else no need to filter.
Please mark that only record which is having status = 'CLSRM' and strtdate/enddate is NULL has to be filtered out.

So my expected output should come like below:

id	name	status	strtdate	enddate
1	A	CLSRM	2014-02-02 00:00:00.000	2014-02-05 00:00:00.000
2	B	CLSRM	2014-02-03 00:00:00.000	2014-02-06 00:00:00.000
3	C	CLSRM	2014-02-04 00:00:00.000	2014-02-07 00:00:00.000
5	E	CLSRM	2014-02-06 00:00:00.000	2014-02-09 00:00:00.000
6	F	ELRN	NULL	NULL
7	G	ELRN	NULL	NULL
8	H	CLSRM	2014-02-09 00:00:00.000	2014-02-12 00:00:00.000
9	I	CLSRM	2014-02-10 00:00:00.000	2014-02-13 00:00:00.000
10	J	CLSRM	2014-02-11 00:00:00.000	2014-02-14 00:00:00.000

Please help me on th

Try the Below Query if you want only the records that have start date and end date as null for status other than that of CLSRM and all the records with CLSRM excepth those with null start date and end date

Select * from @tempTable 
where (status <> 'CLSRM' and strtdate is NULL and enddate is NULL) 
or (status = 'CLSRM' and strtdate is not NULL and enddate is not NULL)

the below query will give you the Records with status CLSRM without null values and the other rows with status not equal to CLSRM

Select * from @tempTable 
where (status <> 'CLSRM') 
or (status = 'CLSRM' and strtdate is not NULL and enddate is not NULL)

Are you looking for the below?

Declare @tempTable table (id int, name varchar(50), status varchar(50), strtdate datetime, enddate datetime)

insert into @tempTable values (   1,     'A',   'CLSRM',      '2-Feb-14',          '5-Feb-14'    )
insert into @tempTable values (   2,     'B',   'CLSRM',      '3-Feb-14',          '6-Feb-14'    )
insert into @tempTable values (   3,     'C',   'CLSRM',      '4-Feb-14',          '7-Feb-14'    )
insert into @tempTable values (   4,     'D',   'CLSRM',        NULL,               NULL         )
insert into @tempTable values (   5,     'E',   'CLSRM',      '6-Feb-14',          '9-Feb-14'    )
insert into @tempTable values (   6,     'F',   'ELRN',         NULL,               NULL         )
insert into @tempTable values (   7,     'G',   'ELRN',         NULL,               NULL         )
insert into @tempTable values (   8,     'H',   'CLSRM',      '9-Feb-14',          '12-Feb-14'   )
insert into @tempTable values (   9,     'I',   'CLSRM',      '10-Feb-14',         '13-Feb-14'   )
insert into @tempTable values (   10,    'J',   'CLSRM',      '11-Feb-14',         '14-Feb-14'   )

Select * from @tempTable where 
1 = case when status = 'CLSRM' And (strtdate is null or enddate is null) then 0 else 1 end

• Marked as answer by Rahul Kumar (Rahul Vairagi) 15 minutes ago

Select * from @tempTable
WHERE (status='CLSRM' AND strtdate IS NOT NULL AND enddate IS NOT NULL ) OR status='ELRN'