query help

Question

query help

sql_learner29

SSCrazy Eights

Points: 9107
More actions
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]

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

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

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 1

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.

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 2

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]

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 3

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?

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 4

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]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 5

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]

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 6

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.

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 7

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]

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 8

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.

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 9

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)

E3A13

E4A23

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]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 10

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]

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 11

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

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 12

You can use two CTEs in a single statement

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

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 13

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]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 14

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]