Results 1 to 3 of 3

Thread: coloumn sorting

  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Unanswered: coloumn sorting

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

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    from the brief look, all your "volatile tables" are based on the same query except the condition on DECLINE_REASON1.
    In that case, it would be ordinary pivot (cross-tab, rows to columns, rotating data, ...) transformation.
    I have no idea whether Teradata (by the way, this is Oracle forum) is able to do such functionality and how. You should investigate it.

    Or, if you plan to use simple SQL, just use the pivoting method prior to Oracle 11g (without PIVOT clause):
    http://www.oracle-base.com/articles/...tors_11gR1.php
    http://www.orafaq.com/wiki/PIVOT
    https://forums.oracle.com/forums/thr...174552#9360005

    As your code is syntactically incorrect in Oracle (GROUP BY column alias, BETWEEN condition on dates), I will post here only frame:
    Code:
    select <grouped columns>
    ,count(case when decline_reason1 = 'Customer Experience' then service end) as "Customer Experience"
    ,count(case when decline_reason1 = 'Prefer another Provider' then service end) as "Prefer another Provider"
    ,count(case when decline_reason1 = 'Coverage/Serviceability' then service end) as "Coverage/Serviceability"
    ,count(case when decline_reason1 = 'Costs' then service end) as "Costs"
    ,count(case when decline_reason1 = 'Service No Longer Required' then service end) as "Service No Longer Required"
    ,count(case when decline_reason1 = 'Customer Usage' then service end) as "Customer Usage"
    from <used tables with join conditions>
    where <filter conditions except the one on DECLINE_REASON1>
    group by <grouped columns without DECLINE_REASON1>;

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by vanselanne View Post
    using Teradata
    You did read the name of this forum did you? It's called Oracle which is a completely different beast than Teradata.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •