Post reply

query help

  • July 13, 2010 at 1:24 pm

    #235106

    Hi all,

    I have to form a query from this input data, the query output should show for every employee

    number of accounts handled and no of resolutions provided,

    The main problem is identifying the resolution provided as it is based on the last employee who handles that call..

    which is determined by the counter attached..

    sample code

    ------------------------------------------------

    CREATE TABLE [dbo].[abcd](

    [EMP] [nvarchar](5) ,

    [order_no] [nvarchar](14) ,

    [counter] [int]

    )

    ------------------------------------------------

    INSERT INTO dbo.abcd ( EMP,order_no,counter) VALUES ( 'E1', 'A1', 1)

    INSERT INTO dbo.abcd ( EMP,order_no,counter) VALUES ( 'E2', 'A1', 2)

    INSERT INTO dbo.abcd ( EMP,order_no,counter) VALUES ( 'E3', 'A1', 3)

    INSERT INTO dbo.abcd ( EMP,order_no,counter) VALUES ( 'E1', 'A2', 1)

    INSERT INTO dbo.abcd ( EMP,order_no,counter) VALUES ( 'E3', 'A2', 2)

    INSERT INTO dbo.abcd ( EMP,order_no,counter) VALUES ( 'E4', 'A2', 3)

    select * from abcd

    Ouput should be like this:

    ------------------------------------------------

    Emp / No_of_accounts/ resolutioncount

    E1 2 0

    E2 1 0

    E3 2 1

    E4 1 1

    ------------------------------------------------

    Any help is appreciated....

    Thanks for your help

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • July 13, 2010 at 1:51 pm

    #1192314

    I'm not sure what the last column in your output means.

    Also, we are happy to help, but we shouldn't be doing all the work. You should attempt to run a query yourself to group these up. As a hint, you'll use COUNT and GROUP BY empid.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 13, 2010 at 1:55 pm

    #1192319

    Hi,

    thanks for reply,

    Actually this last column is only giving me hard time

    No of accounts handled can be found by as you say:

    select * from abcd

    select emp,count(distinct order_no )from abcd

    group by emp

    order by 2 desc

    but now they want me to add one more column in the result showing the employee who took the order LAST, showing that he provided a resolution for the order max(counter), but i am unable to put them all together in one query...

    Hope you are clear..

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • July 13, 2010 at 1:59 pm

    #1192324

    What's "last". You don't show an ordering column. Or do you mean the counter column denotes which one is last? Is it the max value?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 13, 2010 at 2:00 pm

    #1192325

    yes the counter column shows you who gave the resolution for that order..

    max value, but how do i put them together

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • July 13, 2010 at 2:03 pm

    #1192328

    now this is also number of resolutions given as an emp may handle more than one order...

    so I want count for no of max counters handled by that employee..

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • July 13, 2010 at 2:09 pm

    #1192332

    I think you have two queries here. You want a count by employee, but then the last employee based on order. Is that right? Seems like you are pulling together data that doesn't make sense. A count by the employee per order, but the last employee on that same row? Not sure how those relate.

    You could use CTEs to pull this data and then join them together. This CTE gets the order_no with the counter value you need for the emp.

    WITH ResolutionCTE (order_no, maxorder)

    AS

    ( SELECT order_no, MAX(COUNTER) 'maxorder'

    FROM abcd

    GROUP BY order_no

    )

    You could use another CTE to for the count by employee, then join them together.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 13, 2010 at 2:27 pm

    #1192339

    The requirement is not for find max counter for Order no it is to find max by employee I cannot use this...

    ;WITH ResolutionCTE (order_no, maxorder)

    AS

    ( SELECT order_no, MAX(COUNTER) 'maxorder'

    FROM abcd

    GROUP BY order_no

    )

    select * from ResolutionCTE

    as I cannot join it with other query and get the max counter with result like this

    ------------------------------------------------

    Emp / No_of_accounts/ resolutioncount

    E1 2 0

    E2 1 0

    E3 2 1

    E4 1 1

    ------------------------------------------------

    The query should show emp performance

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • July 13, 2010 at 2:31 pm

    #1192344

    I'm saying you need 3 queries, not 2, to join together. One for each requirement, then one to pull them together.

    I don't understand what the 0 or 1 are in the last column. It doesn't seem to make sense.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 13, 2010 at 2:41 pm

    #1192352

    The last column shows the number of resolutions given by that employee number...

    here the employee who has max(counter) is supposed to have given resolution for an order As in the data showing two orders..

    the max counters corresponds to only E3 and E4,

    So they will be counted here (hence 1) not other 2 employess (hence 0)

    E3 A1 3

    E4 A2 3

    Actually this is a performance for employee check query to see how many orders he handled and how many he resolved, unless the counter stops incrementing for an account the order is not resolved as the counter stops (the last employee has resolved , where counter = max value)

    Hope its clear

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • July 13, 2010 at 3:20 pm

    #1192367

    I did it in one way but very complicated one, let me know if any better way is there to do the same

    ; with empcte

    as

    (

    select emp,count(distinct order_no ) as noofOreder from abcd

    group by emp

    )

    select * into #t1 from empcte

    ; WITH ResolutionCTE (order_no, maxorder)

    AS

    (

    SELECT order_no, MAX(COUNTER) 'maxorder'

    FROM abcd GROUP BY order_no

    )

    select * into #t2 from abcd

    where [COUNTER] in(

    select maxorder from ResolutionCTE

    )

    --------join query after wards

    select t.emp,t.noofOreder,

    coalesce(count(order_no),0) as CNT from

    #t1 t left join #t2 t2

    on(

    t.emp=t2.emp)

    group by t.emp,t.noofOreder

    order by 1

    Thanks

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • July 13, 2010 at 3:20 pm

    #1192368

    Is this what you want for the counter?

    WITH ResolutionCTE (order_no, cntr)

    AS

    (

    SELECT order_no, MAX(COUNTER) 'cntr'

    FROM abcd

    GROUP BY order_no

    )

    SELECT emp, COUNT(*)

    FROM abcd a

    INNER JOIN ResolutionCTE r

    ON a.order_no = r.order_no

    AND a.COUNTER = r.cntr

    GROUP BY a.emp

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 13, 2010 at 3:27 pm

    #1192371

    You can use two CTEs in a single statement

    http://qa.sqlservercentral.com/articles/Common+Table+Expression+%28CTE%29/62291/

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 13, 2010 at 3:51 pm

    #1192383

    ok let me try like this but i have a cte inside another CTE that is not shown here, the one you gave me...

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • July 14, 2010 at 8:39 am

    #1192861

    Hi

    Thanks for your help...

    but the query is taking lot of time as I have 500,000+ rows..... and also testing this will be huge task for me.........

    Is there any other way to do the same instead to doing in complicated CTEs then doing a left join from 1 CTEs..( I dont really trust left joins, I will have to test it a lot..)

    Let me know if there is a better way, until then this is working fine...

    Thanks

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply