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
 General SQL Server Forums
 New to SQL Server Programming
 Coloumn Sorting
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vanselanne
Starting Member

Australia
1 Posts

Posted - 10/23/2012 :  22:32:05  Show Profile  Reply with Quote
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 statement

The 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 Usage
name 1 xxxx xxxx 15 27 10 3 7 22 6
Name 2 xxxx xxxx 19 6 29 22 10 42 4


and my code is....

--- Hardware fault ---
create volatile table hardware as
(
select
CSR_FirstName||' '||CSR_LastName as AgentName
,emp_number
,Manager_empl_no
,count(service) as "Hardware/Fault"
from ipshare.ORR_retention_database a
full join RMOIDS
on emp_number = Employee_No
where 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
(
select
CSR_FirstName||' '||CSR_LastName as AgentName
,emp_number
,Manager_empl_no
,count(service) as "Customer Experience"
from ipshare.ORR_retention_database a
full join RMOIDS
on emp_number = Employee_No
where 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 Provider
create volatile table pap as
(
select
CSR_FirstName||' '||CSR_LastName as AgentName
,emp_number
,Manager_empl_no
,count(service) as "Prefer another Provider"
from ipshare.ORR_retention_database a
full join RMOIDS
on emp_number = Employee_No
where 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/Serviceability
create volatile table coverage as
(
select
CSR_FirstName||' '||CSR_LastName as AgentName
,emp_number
,Manager_empl_no
,count(service) as "Coverage/Serviceability"
from ipshare.ORR_retention_database a
full join RMOIDS
on emp_number = Employee_No
where 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;

---Cost

create volatile table cost as
(
select
CSR_FirstName||' '||CSR_LastName as AgentName
,emp_number
,Manager_empl_no
,count(service) as "Costs"
from ipshare.ORR_retention_database a
full join RMOIDS
on emp_number = Employee_No
where 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 required
create volatile table snlr as
(
select
CSR_FirstName||' '||CSR_LastName as AgentName
,emp_number
,Manager_empl_no
,count(service) as "Service No Longer Required"
from ipshare.ORR_retention_database a
full join RMOIDS
on emp_number = Employee_No
where 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 Usage
create volatile table usage as
(
select
CSR_FirstName||' '||CSR_LastName as AgentName
,emp_number
,Manager_empl_no
,count(service) as "Customer Usage"
from ipshare.ORR_retention_database a
full join RMOIDS
on emp_number = Employee_No
where 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 a
inner join custex b
on a.emp_number = b.emp_number
inner join pap c
on a.emp_number = c.emp_number
inner join coverage d
on a.emp_number = d.emp_number
inner join cost e
on a.emp_number = e.emp_number
inner join snlr f
on a.emp_number = f.emp_number
inner join usage g
on a.emp_number = g.emp_number

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 10/23/2012 :  22:55:37  Show Profile  Reply with Quote
you are not using MS SQL Server right ?

For Oracle question, try posting at dbforums.com


KH
Time is always against us

Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 10/24/2012 :  02:41:58  Show Profile  Reply with Quote
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:


select
CSR_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 a
full join RMOIDS
on emp_number = Employee_No
where 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



- Lumbago
My blog-> http://thefirstsql.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.22 seconds. Powered By: Snitz Forums 2000