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 2008 Forums
 Transact-SQL (2008)
 Multiple Rows into One

Author  Topic 

Deadveloper
Starting Member

3 Posts

Posted - 2012-04-12 : 17:47:57
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

227 Posts

Posted - 2012-04-13 : 05:08:31
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

227 Posts

Posted - 2012-04-13 : 05:17:21
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

3 Posts

Posted - 2012-04-13 : 11:08:25
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

52326 Posts

Posted - 2012-04-13 : 15:45:58
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

3 Posts

Posted - 2012-04-16 : 11:01:05
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

52326 Posts

Posted - 2012-04-16 : 11:37:29
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
   

- Advertisement -