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 |
vanselanne
Starting Member
1 Post |
Posted - 2012-10-23 : 22:32:05
|
Hi All,using Teradata, Have data that in one coloum thats has 6 options, what i would like is have each of the six options in a seperate coloum with the count of how many entries it has, at the moment i have 6 sepearate select statments and one selected at the bottom joining it all together, is there anyway to get it in one statementThe output looks like this...AgentName emp_number Manager_Empl_No Hardware/Fault Customer Experience Prefer another Provider Coverage/Serviceability Costs Service No Longer Required Customer Usagename 1 xxxx xxxx 15 27 10 3 7 22 6Name 2 xxxx xxxx 19 6 29 22 10 42 4and my code is....--- Hardware fault --- create volatile table hardware as ( selectCSR_FirstName||' '||CSR_LastName as AgentName,emp_number,Manager_empl_no,count(service) as "Hardware/Fault"from ipshare.ORR_retention_database afull join RMOIDSon emp_number = Employee_Nowhere date_created between (sel start_date from startend_date) and (sel end_date from startend_date)and referred_product = 'Mobile'and decline_reason1 = 'Hardware/Fault'group by (AgentName,emp_number,Manager_empl_no,decline_reason1)qualify RANK() over (partition by AgentName order by AgentName DESC)=1) with data on commit preserve rows;sel * from hardware--- Customer Experience --- create volatile table custex as ( selectCSR_FirstName||' '||CSR_LastName as AgentName,emp_number,Manager_empl_no,count(service) as "Customer Experience"from ipshare.ORR_retention_database afull join RMOIDSon emp_number = Employee_Nowhere date_created between (sel start_date from startend_date) and (sel end_date from startend_date)and referred_product = 'Mobile'and decline_reason1 = 'Customer Experience'group by (AgentName,emp_number,Manager_empl_no,decline_reason1)qualify RANK() over (partition by AgentName order by AgentName DESC)=1) with data on commit preserve rows;---Prefer another Providercreate volatile table pap as ( selectCSR_FirstName||' '||CSR_LastName as AgentName,emp_number,Manager_empl_no,count(service) as "Prefer another Provider"from ipshare.ORR_retention_database afull join RMOIDSon emp_number = Employee_Nowhere date_created between (sel start_date from startend_date) and (sel end_date from startend_date)and referred_product = 'Mobile'and decline_reason1 = 'Prefer another Provider'group by (AgentName,emp_number,Manager_empl_no,decline_reason1)qualify RANK() over (partition by AgentName order by AgentName DESC)=1) with data on commit preserve rows;----Coverage/Serviceabilitycreate volatile table coverage as (selectCSR_FirstName||' '||CSR_LastName as AgentName,emp_number,Manager_empl_no,count(service) as "Coverage/Serviceability"from ipshare.ORR_retention_database afull join RMOIDSon emp_number = Employee_Nowhere date_created between (sel start_date from startend_date) and (sel end_date from startend_date)and referred_product = 'Mobile'and decline_reason1 = 'Coverage/Serviceability'group by (AgentName,emp_number,Manager_empl_no,decline_reason1)qualify RANK() over (partition by AgentName order by AgentName DESC)=1) with data on commit preserve rows;---Costcreate volatile table cost as ( selectCSR_FirstName||' '||CSR_LastName as AgentName,emp_number,Manager_empl_no,count(service) as "Costs"from ipshare.ORR_retention_database afull join RMOIDSon emp_number = Employee_Nowhere date_created between (sel start_date from startend_date) and (sel end_date from startend_date)and referred_product = 'Mobile'and decline_reason1 = 'Costs'group by (AgentName,emp_number,Manager_empl_no,decline_reason1)qualify RANK() over (partition by AgentName order by AgentName DESC)=1) with data on commit preserve rows;--Service no longer requiredcreate volatile table snlr as ( selectCSR_FirstName||' '||CSR_LastName as AgentName,emp_number,Manager_empl_no,count(service) as "Service No Longer Required"from ipshare.ORR_retention_database afull join RMOIDSon emp_number = Employee_Nowhere date_created between (sel start_date from startend_date) and (sel end_date from startend_date)and referred_product = 'Mobile'and decline_reason1 = 'Service No Longer Required'group by (AgentName,emp_number,Manager_empl_no,decline_reason1)qualify RANK() over (partition by AgentName order by AgentName DESC)=1) with data on commit preserve rows;--Customer Usagecreate volatile table usage as ( selectCSR_FirstName||' '||CSR_LastName as AgentName,emp_number,Manager_empl_no,count(service) as "Customer Usage"from ipshare.ORR_retention_database afull join RMOIDSon emp_number = Employee_Nowhere date_created between (sel start_date from startend_date) and (sel end_date from startend_date)and referred_product = 'Mobile'and decline_reason1 = 'Customer Usage'group by (AgentName,emp_number,Manager_empl_no,decline_reason1)qualify RANK() over (partition by AgentName order by AgentName DESC)=1)with data on commit preserve rows;sel a.* ,b."Customer Experience",c."Prefer another Provider",d."Coverage/Serviceability",e."Costs",f."Service No Longer Required",g."Customer Usage"from hardware ainner join custex bon a.emp_number = b.emp_numberinner join pap con a.emp_number = c.emp_numberinner join coverage don a.emp_number = d.emp_numberinner join cost eon a.emp_number = e.emp_numberinner join snlr fon a.emp_number = f.emp_numberinner join usage gon a.emp_number = g.emp_number |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-10-23 : 22:55:37
|
you are not using MS SQL Server right ?For Oracle question, try posting at dbforums.com KH[spoiler]Time is always against us[/spoiler] |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-10-24 : 02:41:58
|
This forum is for Microsoft SQL Server so not very many people here know the works of Teradata. However, I'm sure there is a way to port the following to a sql query that works in Teradata as well:selectCSR_FirstName||' '||CSR_LastName as AgentName,emp_number,Manager_empl_no,sum(case when decline_reason1 = 'Hardware/Fault' then 1 else 0 end) as "Hardware/Fault",sum(case when decline_reason1 = 'Customer Experience' then 1 else 0 end) as "Customer Experience"...from ipshare.ORR_retention_database afull join RMOIDSon emp_number = Employee_Nowhere date_created between (sel start_date from startend_date) and (sel end_date from startend_date)and referred_product = 'Mobile'and decline_reason1 = 'Hardware/Fault'group by (AgentName,emp_number,Manager_empl_no,decline_reason1)qualify RANK() over (partition by AgentName order by AgentName DESC)=1 - LumbagoMy blog-> http://thefirstsql.com |
|
|
|
|
|
|
|