| Author |
 Topic  |
|
|
vanselanne
Starting Member
Australia
1 Posts |
 Posted - 10/23/2012 : 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 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
16745 Posts |
Posted - 10/23/2012 : 22:55:37
|
you are not using MS SQL Server right ?
For Oracle question, try posting at dbforums.com
KH
Time is always against us
|
 |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 10/24/2012 : 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:
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 |
|
|
| |
Topic |
|
|
|
Coloumn Sorting
