SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Multiple Rows into One
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Deadveloper
Starting Member

USA
3 Posts

Posted - 04/12/2012 :  17:47:57  Show Profile  Reply with Quote
Hi,

I have a view that I am using to get the results of a CSR Phone call that has been evaluated. The call to the view has parameters of @StartDate, @EndDate, AND @AgentID.

Returned fields are: CategoryName, CategoryOrder, QuestionText, Pass

The data comes back like so:
Greeting 1 Did CSR use the proper branded greeting? 1
Discover 2 Did CSR express empathy? 1
Discover 2 Did CSR reassure the client that they've called the right place? 1
Discover 2 Did CSR restate the problem and probe for any other required/relevant information? 1
Listen 3 Did CSR inquire how long the issue has existed, gather energy source and equipment location? 1
Listen 3 Did CSR ask for the age of the equipment (or home)? 1
Listen 3 Did CSR ask for the lead source? (required for every call including Express Calls, Club Members, and Current Clients) 0
Listen 3 Did CSR search for an existing service location and complete/verify client`s information? (Name, address, phone number, alternate phone number, and homeowner/occupant) 0
Listen 3 Did CSR complete/verify client’s email address? 1
Listen 3 Did CSR complete clients cross streets? 0
Listen 3 Did CSR review/acknowledge client history, warranty, warranty disclaimer, installation, and club info as appropriate? 1

Each one is a different question even tho the CategoryName's can be the same, e.g. 'Listen'.

The integer at the end is if they passed or not, but it is a bit field.

Also there can be multiple evaluations in the returned view.

So how can I go about checking each row and get a total for passed on each question, which I need to SUM togther to return one row that has all fields with totals for each question?

Something like:
Agent Greeting Empathy Reassurance ............ect
Meg Ryan 1 2 1
Bill Smith 1 1 2

Programming is like sex, one mistake and you end up supporting it the rest of your life.

vinu.vijayan
Posting Yak Master

India
227 Posts

Posted - 04/13/2012 :  05:08:31  Show Profile  Reply with Quote
From what i understand...I think you want the count of Pass fiels. You can run the following query to do that:

Select COUNT(Pass) From Ex Where Pass = 1


If not this then you must be looking to sum the field "Pass" on the basis of some other field. That is still unanswered. On the basis of which column do you want "Pass" to be counted?

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"
Go to Top of Page

vinu.vijayan
Posting Yak Master

India
227 Posts

Posted - 04/13/2012 :  05:17:21  Show Profile  Reply with Quote
Sorry for the last post...misunderstood what you were asking. My bad.
You want the Total of passed attempts for each question. Right?
Here's the query:


Select QuestionText ,COUNT(Pass) From Ex Where Pass = 1
Group By QuestionText




N 28° 33' 11.93148"
E 77° 14' 33.66384"
Go to Top of Page

Deadveloper
Starting Member

USA
3 Posts

Posted - 04/13/2012 :  11:08:25  Show Profile  Reply with Quote
What I ended up doing was this:
CONVERT(varchar(20),SUM(Greeting + Empathy + Reassurance + [Restate and Probe] + [Equip Info] + Age + [Lead Src] +
Location + EmailAddress + [Cross Streets] + History + [2 Appts] + Fees + Club + Pymt + Confirm + [Addl Assist] + [Branded Close] +
Etiquettte + Enthusiasm + Documentation + [Correct Procedures] + value + Rebuttals + Objections) OVER (PARTITION BY Agent),1) AS Total

Programming is like sex, one mistake and you end up supporting it the rest of your life.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/13/2012 :  15:45:58  Show Profile  Reply with Quote
how are you linking this to agent? Are they stored in different table? I cant see them in posted data though

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Deadveloper
Starting Member

USA
3 Posts

Posted - 04/16/2012 :  11:01:05  Show Profile  Reply with Quote
Agent is also part of the query. I took it out for ease of reading. Agent would be the persons name. I am pulling AgentID, Agent, then the rest of the fields I have in above post.

Programming is like sex, one mistake and you end up supporting it the rest of your life.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/16/2012 :  11:37:29  Show Profile  Reply with Quote
Ok...then thats fine
Was checking on it as i couldnt understand how you were linking it in result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000