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

Transact-SQL (2005)

Need help with query

Author

Topic

BZboy
Starting Member

9 Posts

Posted - 2009-03-12 : 14:32:20

Hello all. I am new here but hope I can get some assistance =)

I have a table with some of the following columns: "description", "loan#", "datedate" etc.

I need help with a query to pull all of the loan numbers that have the same description code within a 7 day timeframe.

all I have so far is:

select *
from dbo.loan_table
where description like 'answering machine'
and jobdate between '2009-01-01' and '2009-01-10'
order by jobdate

any help would be appreciated

thank you!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-12 : 14:46:55

Are you getting an error with this query? Whats the problem?

BZboy
Starting Member

9 Posts

Posted - 2009-03-12 : 15:33:00

Not getting an error at all. that query runs fine, what I need is help adding on to/changing that query to give me all of the loan numbers that have the same description code in the same 7 day timeframe.

maybe this will help explain better. Say a account/loan holder is called all of the time and an agent keeps gettign the answering machine. I need to produce a query that will pull all of the loan numbers/people that have been called and their answering machine has picked up everytime for the last seven days.

loan number= loan_num field
answering machine is description field
jobdate = datadate

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 16:51:12

See http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

BZboy
Starting Member

9 Posts

Posted - 2009-03-16 : 09:17:44

Funny Peso, so are you saying you didnt understand the question? I re-read the question, seems pretty clear to me. or possibly im in the wrong forumns for help?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-03-16 : 09:37:17

even I'm not very clear on the question. what is that your query is not doing?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 10:13:33

This???

select 
	distinct loan_num  
from 
	dbo.loan_table
where 
	jobdate between '2009-01-01' and '2009-01-10'
group by 
	loan_num field,[description]
having 
	count(*)>1

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 10:19:39

Above will list loan_num for all kind of descriptions. To limit it to just description of 'answering machine', put that filter you have in your first post.

To list other details, use this..

Select 
	a1.loan_num,a1.[description],a1.jobdate 
from 
	dbo.loan_table a1 join
	(
	select 
		distinct loan_num  
	from 
		dbo.loan_table
	where 
		jobdate between '2009-01-01' and '2009-01-10'
	group by 
		loan_num,[description]
	having 
		count(*)>1
	)a
	on a.loan_table=a1.loan_table and a1.jobdate between '2009-01-01' and '2009-01-10'

Hope that is what you want.

BZboy
Starting Member

9 Posts

Posted - 2009-03-16 : 10:23:39

ok Ill try to make it clearer.

here is what I need:
for example, say there are are 4 columns: loan number, telephone #, telephone code, datadate.

I have collectors that call customers and sometimes get their answering machine. I need a query that will list all of the loan numbers/customers that have been called and their answering machine has picked up for 7 days straight.

The query above in my first post just gives a basic start to what I have so far.

here is my question: What can I add to the above query that will show all the loan numbers/accounts that have been called and collectors have recieved the answering machine 7 days in a row?

If I still need to explain frutther please let me know and I will be glad to do so. Thanks for all of the help and sorry for confusion.

P.S. I tried tohe above and it didnt work for me =( "having count(*) >6

BZboy
Starting Member

9 Posts

Posted - 2009-03-16 : 10:35:25

Sakets

THANK YOU! yes that is very close to what I need. I really appreciate the help. That query seems to be running pretty close to what I need. I just need to change a few things around to correlate with the table(s) that im using and it should be good! thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 10:57:53

quote:
Originally posted by BZboy

Funny Peso, so are you saying you didnt understand the question?

Not only me. Three other people were having trouble understanding your scenario.
If you have read the link I provided, you would understand why.

Of four people you got 5 answers. 1 answer happened to be close to what you wanted...
If you had been more clear and wrote better description I believe you would have got the correct answer with first reply.

E 12°55'05.63"
N 56°04'39.26"

BZboy
Starting Member

9 Posts

Posted - 2009-03-16 : 12:16:23

I understand. I guess I thought this was pretty understandable:

"I need help with a query to pull all of the loan numbers that have the same description code within a 7 day timeframe. "

"Not getting an error at all. that query runs fine, what I need is help adding on to/changing that query to give me all of the loan numbers that have the same description code in the same 7 day timeframe"

"Say a account/loan holder is called all of the time and an agent keeps gettign the answering machine. I need to produce a query that will pull all of the loan numbers/people that have been called and their answering machine has picked up everytime for the last seven days."

next time i will spell it out step by step, to make it very understandable

thanks again for the help everyone. next time I will try to make it easier to understand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 12:55:05

what determines whether users have picked up? what suggests they're called by loan?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources