Coloumn Sorting - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Coloumn Sorting

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 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)

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:


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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources