help with a 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 a query

Author

Topic

trans53
Starting Member

2 Posts

Posted - 2006-02-28 : 00:11:47

Having records like this in a table:

DATETIME ACCOUNT_NUMBER OLD_VALUE NEW_VALUE
02/27/2006 18:00:03.000 149 'suspend' 'negative'
02/26/2006 15:00:03.000 149 'negative' 'suspend'

02/26/2006 14:00:03.000 23 'negative' 'suspend'

I need to get ONLY those accounts who has just one value for new_value = 'suspend' for February.
If the account has 2 or more values then skip this account.
Any ideas?

Thank you

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-28 : 00:22:43

Hi,
First of all avoid using Keywords as column names
select account,count(account) from table
where new_value = 'suspend' and month(datetime) = 2
group by account
having count(account) = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources