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.
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, PassThe data comes back like so:Greeting 1 Did CSR use the proper branded greeting? 1Discover 2 Did CSR express empathy? 1Discover 2 Did CSR reassure the client that they've called the right place? 1Discover 2 Did CSR restate the problem and probe for any other required/relevant information? 1Listen 3 Did CSR inquire how long the issue has existed, gather energy source and equipment location? 1Listen 3 Did CSR ask for the age of the equipment (or home)? 1Listen 3 Did CSR ask for the lead source? (required for every call including Express Calls, Club Members, and Current Clients) 0Listen 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) 0Listen 3 Did CSR complete/verify client’s email address? 1Listen 3 Did CSR complete clients cross streets? 0Listen 3 Did CSR review/acknowledge client history, warranty, warranty disclaimer, installation, and club info as appropriate? 1Each 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 ............ectMeg Ryan 1 2 1Bill Smith 1 1 2Programming 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 VijayanN 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
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 = 1Group By QuestionText N 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
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 TotalProgramming is like sex, one mistake and you end up supporting it the rest of your life. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|