Query help - I think this one is easy......

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

Transact-SQL (2000)

Query help - I think this one is easy......

Author

Topic

acribb
Starting Member

19 Posts

Posted - 2006-11-30 : 15:56:04

I am trying to put together a query and I could definitely use anyone's help.

Within a table, I am looking at the field named 'actualLoanOfficerId'. I need to select all distinct records where there are 20 or more records that contain the same actualLoanOfficerId. For example, there are 30 records that have the same actualLoanOfficerId of ABCD, so that would be one result I would like to see. Then there are 23 records that have an actualLoanOfficerId of 12345, that is another result I would like to see. Etc., Etc,

Can you help with this query?

Thanks for any help.....

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2006-11-30 : 16:18:47

create table #Yourtable (
c1 int identity(1,1),
actualLoanOfficerId varchar(10)
)

insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('123')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('ABC')
insert into #Yourtable (actualLoanOfficerId) values ('NOPE')
insert into #Yourtable (actualLoanOfficerId) values ('NOPE')
insert into #Yourtable (actualLoanOfficerId) values ('NOPE')
insert into #Yourtable (actualLoanOfficerId) values ('NOPE')
insert into #Yourtable (actualLoanOfficerId) values ('NOPE')

Select *
from #Yourtable
WHERE actualLoanOfficerId in
(select actualLoanOfficerId from
(select actualLoanOfficerId, count(*) as ct
from #Yourtable
group by actualLoanOfficerId
having count(*) >= 20
)a
)

drop table #Yourtable

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

acribb
Starting Member

19 Posts

Posted - 2006-11-30 : 16:43:44

Thanks, but i'm not sure if this is working. When I run this:

Select *
from dbo.UniversalLeads
WHERE actualLoanOfficerId in
(select actualLoanOfficerId from
(select actualLoanOfficerId, count(*) as ct
from dbo.UniversalLeads
group by actualLoanOfficerId
having count(*) >= 20
)a
) and leadTypeId = 'Service Quality Survey'

When I sort the results by actualLoanOfficerId, I can see results with less than 20.

Any ideas? Is it because I added "and leadTypeId = 'Service Quality Survey'"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 16:49:39

duplicate post!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75657

Peter Larsson
Helsingborg, Sweden

acribb
Starting Member

19 Posts

Posted - 2006-11-30 : 17:08:52

Sorry about the cross-posting. First time posting here and I didn't realize I had put my post in the wrong forum, so I posted it over here.

Also, I don't think this is working.

When I sort the results by actualLoanOfficerId, I can see that the first group of records have only 14 records with an actualLoanOfficerId rather than the required 20 or more.

Any ideas?

Thanks again for everyone's input thus far..

acribb
Starting Member

19 Posts

Posted - 2006-11-30 : 17:26:00

This seems to have worked:

select actualLoanOfficerId, count(*) as ct
from dbo.UniversalLeads where leadTypeId = 'Service Quality Survey'
group by actualLoanOfficerId
having count(*) >= 20

Thanks again for everyone's help. I'll be back soon!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources