Help with SQL (group by or self join???)

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 2005 Forums

Transact-SQL (2005)

Help with SQL (group by or self join???)

Author

Topic

sriram.ranga
Starting Member

1 Post

Posted - 2008-12-10 : 10:27:48

Hois,

My DB is SQL Server, so even some specific non-standard SQL is fine for this (standard ANSI is preferred).

There is a table called ACTIVITY with two fields called ACTIVITY_ID and STATUS. (there are other fields but these are the ones I am concerned with).

The possible values for STATUS are 'Y' and 'N' (characters). Activity ID is a string and can be something like '1-TXM2F'.

Note - The table can have multiple entries for the same ACTIVITY_ID. i.e. - there can be one entry for ACTIVITY_ID = '1-TXM2F' with STATUS = 'Y' and another with STATUS = 'N'.

Through my query, I want to get all Activities (identified by ACTIVITY_ID) from the ACTIVITY table which have only one entry for a given ACTIVITY_ID and that entry must have STATUS = 'Y'

SO, I don't want the following records:

1. Activities for which there are multiple Records (i.e. - multiple records with same ACTIVITITY_ID)

2. Activities which might only have one record, but the STATUS of which might be 'N'.

So I wrote the following SQL:

select ACTIVITY_ID,COUNT(ACTIVITY_ID) as ct from S_ACT_EMP
group by ACTIVITY_ID, ROW_STATUS
HAVING COUNT(ACTIVITY_ID) = 1 and ROW_STATUS = 'Y'

Problem is that it also returns all activities for which there are two entries - one with STATUS = 'Y' and one with STATUS = 'N'. i.e. - it doesn't consider that I "don't" want the activities for which there might be a record with both STATUS = 'Y' and STATUS = 'N'.

Then I wrote the following SQL:

select ACTIVITY_ID from S_ACT_EMP WHERE ACTIVITY_ID IN (
select ACTIVITY_ID
from S_ACT_EMP
group by ACTIVITY_ID
HAVING COUNT(ACTIVITY_ID) = 1) AND ROW_STATUS = 'Y'

I think it does the job but it looks ugly .... Can it be made better, performance wise? This table has millions of records and any improvement is good.

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 10:31:54

[code]select ACTIVITY_ID,COUNT(ACTIVITY_ID) as ct from S_ACT_EMP
group by ACTIVITY_ID
HAVING COUNT(DISTINCT ROW_STATUS) = 1
and SUM(CASE WHEN ROW_STATUS = 'Y' THEN 1 ELSE 0 END)>0[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources