2 tables - 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

General SQL Server Forums

New to SQL Server Programming

2 tables

Author

Topic

andyb7274
Starting Member

4 Posts

Posted - 2009-11-24 : 03:19:08

Hi guys, I am very new to SQL and have been playing with simple queries so far, but I am now stumped by what I assume will be relatively simple, the syntax i am using seems logical enough but I can't get it to work.

Basic idea is this, we are using SQL server 2005 and I would like to extract the number of open calls per service desk agent.

Table names
richuser.fullname
calls.callid

This is what I have used so far, in my head this should give the number of calls open (status=2) for each username from Richuser.fullname

SELECT RichUser.FullName, Count(calls.Callid)
FROM {oj Sunrise2005.dbo.calls calls LEFT OUTER JOIN Sunrise2005.dbo.RichUser RichUser ON calls.Callid = RichUser.FullName}
WHERE (calls.Status=2)
GROUP BY RichUser.FullName

With the above code I get a blank Fullname column but the total number of calls with status 2

Fullname count(Callid)
<blank> 549

Obviously I am doing something horribly wrong, and this may be way above my learning curve at the moment, but any heads up or clues would be appreciated, and no this is not a homework question more of a self development help.

Many thanks in advance

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-24 : 04:00:32

Provide more information on table Structure and sample data..
So that we can easily provide solution for you..

Balaji.K

andyb7274
Starting Member

4 Posts

Posted - 2009-11-24 : 04:17:40

quote:
Originally posted by kbhere

Provide more information on table Structure and sample data..
So that we can easily provide solution for you..

Balaji.K

Hi, thanks for the quick response, I hope the following is enough.

Sample data
Richuser.fullname

joe bloggs
fred smith
joe smith
fred bloggs

Calls.callid

it21111
it21112
it21113
it21114

Expected output

Fullname | count(callsid)

fred bloggs | 3
joe smith | 7
fred smith | 4
Joe bloggs | 6

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-25 : 01:26:48

What is the relation between the two tables?

Madhivanan

Failing to plan is Planning to fail

andyb7274
Starting Member

4 Posts

Posted - 2009-11-25 : 04:10:22

quote:
Originally posted by madhivanan

What is the relation between the two tables?

Madhivanan

Failing to plan is Planning to fail

This could be where I'm falling down then, I don't know (as I said I'm very new to SQL) I don't know if there is a relationship between the two tables.

Many thanks for your reply

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-25 : 04:23:06

See what this returns

exec sp_depends 'Richuser'

Madhivanan

Failing to plan is Planning to fail

andyb7274
Starting Member

4 Posts

Posted - 2009-11-25 : 09:02:15

quote:
Originally posted by madhivanan

See what this returns

exec sp_depends 'Richuser'

Madhivanan

Failing to plan is Planning to fail

Hi, I appreciate your time with this, I do not have direct access the server tools, and am using a database manager supplied with the helpdesk system, so unfortunately your suggestion does not give me any results, however I find through a feature called index summary is the following :-

dbo.RichUser (4 indexes)
aaaaaRichUser_PK <UserID>
Reference14 <TeamID>
Reference15 <SecurityGroupID>
Reference6 <LanguageID>

does this help at all ??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources