Hi All,

I have included in the attached SQL:

Declare @table1 table
(
	cnsmr_id int,
	complainid int,
	complaintstat varchar(10)
)
Insert into @table1
Select 1,1,''
UNION ALL
Select 1,2,'dfs'
UNION ALL
Select 2,1,NULL

select *from @table1 Where ISNULL(complaintstat,'') = ''

SELECT SUM(Case When ISNULL(complaintstat,'') = '' Then 1 Else 0 End),COUNT(*) ,cnsmr_id FROM @table1 
Group by cnsmr_id
Having SUM(Case When ISNULL(complaintstat,'') = '' Then 1 Else 0 End) = COUNT(*)

The query should return only cnsmr_id=2 since all the complaints is closed (blank/NULL) i have achieved this using having but is there is more performance way of doing this?

Thanks,

Eshwar.

try this..

Declare @table1 table
(
	cnsmr_id int,
	complainid int,
	complaintstat varchar(10)
)
Insert into @table1
Select 1,1,''
UNION ALL
Select 1,2,'dfs'
UNION ALL
Select 2,1,NULL

--Method:1
select * from @table1 where cnsmr_id not in
(select cnsmr_id from @table1 where complaintstat is not null)

--Method:2
select * from @table1 A where not exists 
(select cnsmr_id from @table1 B  where A.cnsmr_id=B.cnsmr_id and complaintstat is not null)

Good day, 

Here are 2 more options.

* I checked the execution plan performance it it is look like solution 4 is the best IN THE CURRENT DDL+DML. When executing the 4 solutions together and check the execution plan I got these values: solution 1 throw 3 use 20% while solution 4 use 10% (30% was the insert)

** These are NOT the same queries! but I am assuming that if you have duplicate rows you just want one of the columns value. I am not 100% sure what is your case  (pls check if this fits your need).

--Method:3
;with MyCTE as (
	select cnsmr_id,complainid, (select COUNT(complaintstat) from @table1 TIn where TIn.cnsmr_id = Tout.cnsmr_id and TIn.complainid = Tout.complainid) CountOpencomplaints
	from @table1 Tout
)
select * from MyCTE 
where CountOpencomplaints = 0

--Method:4
select MAX(cnsmr_id),MAX(complainid)
from @table1 Tout
where not ISNULL(complaintstat,'') = ''

* I used MAX instead of DISTINCT since it was much better here (it need no sorting which cost 78% when I tried it)

This should be simpler:

Declare @table1 table
(
	cnsmr_id int,
	complainid int,
	complaintstat varchar(10)
)
Insert into @table1
Select 1,1,''
UNION ALL
Select 1,2,'dfs'
UNION ALL
Select 2,1,NULL

select cnsmr_id 
from @table1
GROUP BY cnsmr_id
HAVING max(coalesce(complaintstat,'')) = ''

You have no idea what a table is, do you? You have no idea how to design a schema. Or the correct syntax of  an INSERT INTO statement, etc.   

-- repaired !! 
CREATE TABLE Complaints
(consumer_id INTEGER NOT NULL, 
 complaint_id INTEGER NOT NULL, 
 PRIMARY KEY (consumer_id, complaint_id),
 complaint_status VARCHAR(10) DEFAULT 'open' NOT NULL
  CHECK (complaint_status IN ('open', 'closed'..));

INSERT INTO Complaints
VALUES
(1, 1, 'open'), 
(1, 2, 'dfs'), 
(2, 1, 'closed');

Why did you allow '' and NULL in the complaint_status? This design is insane. Oh, we do not use ISNULL() today; we have COALESCE . Thanks to your design, you have to kludge with CASE expressions. NO, NO, NO!

>> The query should return only consumer_id=2 since all the complaints is closed (blank/NULL)[sic] <<

Gee, a competent SQL programmer would have a value for closed; can you be replaced? Your boss need sto get rid of you before you hurt the company.