Solved

Selecting, counting and outputting an ordered list

Posted on 2007-03-21
5
200 Views
Last Modified: 2010-03-19
Dear Experts,

I'm trying to create an SQL query to output each unique 'username' along with a few details about their calls from table 'CallBankResponse'. This table contains a record to indicate that a call (from CallBank) has been completed.

The desired output for my query is:

Username  |  Number of calls completed  |  Number of calls allocated
--------------------------------------------------------------------
user1            40                  50
user2            38                  55
user3            35                  59

Here is the structure of my tables:

CallBankResponse
=====================
Call_ID (FK)

CallBank
=====================
Call_ID (PK)
Username


Any help on this one would be greatly appreciated.

Thanks

NIck
0
Comment
Question by:nkewney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18763002
what determines Calls Completed and Calls Allocated?

Can you show us some sample data?

AW
0
 
LVL 1

Author Comment

by:nkewney
ID: 18763081
Hi Arthur,

Completed calls are reocrds in CallBankResponse whose Call_ID matches that of CallBank

An example:

CallBank
===============
655      4258      iain      3/16/2007 5:33:04 PM      nick      NULL      4/13/2007 5:33:04 PM      33      13
656      4259      iain      3/16/2007 5:37:51 PM      nick      NULL      4/13/2007 5:37:51 PM      33      13
658      4252      iain      3/16/2007 5:43:08 PM      nick      NULL      4/13/2007 5:43:08 PM      33      13
CallBankResponse
=============
655      494      3/15/2007 1:54:44 PM      3/15/2007 1:57:28 PM      3/15/2007 1:57:39 PM
656      495      3/15/2007 1:57:53 PM      3/15/2007 2:00:17 PM      3/15/2007 2:00:29 PM
657      496      3/15/2007 2:00:52 PM      3/15/2007 2:03:27 PM      3/15/2007 2:03:39 PM
658      361      3/15/2007 2:03:20 PM      3/15/2007 2:05:08 PM      3/15/2007 2:08:01 PM
0
 
LVL 1

Author Comment

by:nkewney
ID: 18763224
Apologies, I didn't include the column headers!

CallBank
===============
Call_ID, Outlet_ID, Username, Date
655      4258      iain      3/16/2007 5:33:04 PM      nick      NULL      4/13/2007 5:33:04 PM      33      13
656      4259      iain      3/16/2007 5:37:51 PM      nick      NULL      4/13/2007 5:37:51 PM      33      13
658      4252      iain      3/16/2007 5:43:08 PM      nick      NULL      4/13/2007 5:43:08 PM      33      13
CallBankResponse
=============
Response_ID, Call_ID, Date, Started, Finished
494      655      3/15/2007 1:54:44 PM      3/15/2007 1:57:28 PM      3/15/2007 1:57:39 PM
495      656      3/15/2007 1:57:53 PM      3/15/2007 2:00:17 PM      3/15/2007 2:00:29 PM
496      657      3/15/2007 2:00:52 PM      3/15/2007 2:03:27 PM      3/15/2007 2:03:39 PM
497      658      3/15/2007 2:03:20 PM      3/15/2007 2:05:08 PM      3/15/2007 2:08:01 PM
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 18767731
SELECT cb.Username,
    SUM(CASE WHEN cbr.call_id IS NULL THEN 0 ELSE 1 END) AS [Number of Calls Completed],
    COUNT(*) AS [Number of Calls Allocated]
FROM CallBank cb
LEFT OUTER JOIN CallBankResponse cbr ON cbr.call_id = cb.call_id
GROUP BY cg.Username
0
 
LVL 1

Author Comment

by:nkewney
ID: 18778604
Perfect Scott, Thanks

Nick
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question