Help with Query.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with Query....

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2007-04-04 : 11:58:16

I have a table something like below, that has fields HOUSEHOLD,ACCOUNT, STATUS and many more. A HOUSEHOLD is comprised of many ACCOUNTs under it that has a status of open or closed ('O' OR 'C')

TABLE:

HOUSEHOLD......ACCOUNT.........STATUS
1......................1234.................C
1......................1235.................O
1......................1236.................C
2......................4567.................C
2......................4568.................C
2......................4569.................C
2......................4562.................C
3......................1278.................O
3......................1258.................C
4......................1254.................C
4......................1259.................C
4......................1459.................C

All I am trying to do is to grab the households that has ALL the Account status as closed (Status = ‘C’) . I don’t want Households that has even one Account Open (‘O’)UNDER IT.

From the above query I want to grab Household 2 and 4 as it has all the account associated with it is closed.

I will really appreciate your advice.

Thanks

Raj

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-04 : 12:05:24

May be this?

declare @t table
(
	HOUSEHOLD int,
	Account int,
	Status varchar(1)
)

insert @t
select 1,1234,'C' union all
select 1,1235,'O' union all
select 1,1236,'C' union all
select 2,4567,'C' union all
select 2,4568,'C' union all
select 2,4569,'C' union all
select 2,4562,'C' union all
select 3,1278,'O' union all
select 3,1258,'C' union all
select 4,1254,'C' union all
select 4,1259,'C' union all
select 4,1459,'C'

Select * from @t t1
Join
(
	select household
	from @t t2
	group by household
	having count(*) = (select count(*) from @t t3 where t3.household = t2.household and t3.Status = 'C')
) t3
on t1.household = t3.household

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-04 : 12:07:24

Or more compact and efficient:

declare @t table
(
	HOUSEHOLD int,
	Account int,
	Status varchar(1)
)

insert @t
select 1,1234,'C' union all
select 1,1235,'O' union all
select 1,1236,'C' union all
select 2,4567,'C' union all
select 2,4568,'C' union all
select 2,4569,'C' union all
select 2,4562,'C' union all
select 3,1278,'O' union all
select 3,1258,'C' union all
select 4,1254,'C' union all
select 4,1259,'C' union all
select 4,1459,'C'


Select * from @t t1
Join
(
	select household from @t
	group by household
	having count(*) = sum(case when status = 'C' then 1 else 0 end)
) t2
on t1.household = t2.household

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 12:08:50

Will this do?

SELECT		HouseHold
FROM		@t
GROUP BY	HouseHold
HAVING		MIN(Status) = 'c'
		AND MAX(Status) = 'c'

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-04 : 12:11:47

Much elegant than mine!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 12:19:12

If 'O' and 'C' are the two only possible status, try this for compact code


SELECT		HouseHold
FROM		@t
GROUP BY	HouseHold
HAVING		MAX(Status) = 'c'

Peter Larsson
Helsingborg, Sweden

rkumar28
Starting Member

49 Posts

Posted - 2007-04-04 : 15:11:01

Thanks a bunch for replying....Both worked. I am going for the second one as it little simpler.

Thanks again....

Raj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources