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

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

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

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

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

max value, but how do i put them together

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

ok

Ohh yeah, I have to change it a little, yesterday it was time to leave so could not understand the code, but got it now...

Thanks again

Thank you so much!!!!!!

This is exactly what I wanted..the results are PERFECT!!!!!!!!

I really don't know how this is working but your code is fetching( in seconds :-)) what I wanted...

first of all, Its not that easy as it appears ....so its not a homework...

the input for every Id will have not more than 10 rows and only exception rows...

Thanks for quick response,To make it clear Let me put it like this,

Que:

Find all the rows where Fromdate lies between any other date range for that ID

there can be...

if you are using expressions in your sql task with datetime fields, make sure to use TYPE cast DT_WSTR()

there will be some limitations based on no. of rows, should not exceed 65656, as in excel

but having a look at your erro:DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection...

got it...in TSQL but not in SSIS

SELECT [sno]

,[DATE]

,[setno]

,[type]

...

Thanks a lot Ed for your help....:-)

Your code is gr8 and is working fine....The results are correct..

but since it has an update it is taking lot of time( i...