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

Transact-SQL (2008)

help with a query

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2011-11-22 : 22:34:14

Hello,

I have a problem in a database, I'm in need of some expert advice on how to retrieve this data.
Basically, in the table below, each account number is repeated 3 or 4 times with a code in the second column and each set of account numbers below should have code 01 in it. I'm trying to only select those accounts that do not have code 01. For example, in my results, I would want to see Account_NUM 777333 and 444222 because they do not have code 01 in there. The database I have at work has millions of records and I need to pull out only the account numbers that are missing code 01. At first, I thought this was easy, but its pretty complicated and I've been stuck on it all day. I'm sorry if the format gets messed up, but the column for the code is a two digit character and the account number is 6 digits.

Account_Num Code
555666 67
555666 50
555666 78
555666 01
777333 89
777333 35
777333 54
444222 87
444222 98
444222 54
767767 98
767676 54
767676 55
767767 01

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-22 : 22:38:27

[code]
select *
from yourtable t
where not exists (select * from yourtable x where x.account_num = t.account_num and x.Code = '01')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

joe8079
Posting Yak Master

127 Posts

Posted - 2011-11-22 : 22:50:47

Awesome, thank you!
I didn't think of that. I will try that tomorrow.

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-23 : 11:04:02

Yes i don't think so ,you need to use a right('string',2) function instead of above
the Right function truncates strings at a certain length from the right hand side of that string. The following example produces a 2character ProductCode column from the final 2 characters of the ProductName column:

SELECT ProductName, RIGHT(ProductName, 2) AS ProductCode FROM Products  where productcode='01'

please note that if the productnme column is in number format as your case ,So first you should convert it to char column using casting function

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-23 : 11:15:56

did you reply to the right question?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources