Thanks a lot

i didnt get ur question but if u e asking about which query is corect and the query which has UNION ALL

Not working 🙁

yes . ur query is not returning any row

sample data is not possible , as it includes amny tables

yes row count is correct and time taken = 18 secs

but my concerns is UNION ALL with...

thanks for ur reply but its not working

Gail,

i posted as u suggested as u have asked to do in that link

i have pasted tables definition along with index defintion

but for more detail :...

thanks a lot

the inner derived table ( pasted below ) gives an error

-Query : ------------------------

select

rad.inner_qstn_text,case when rad.inner_qstn_choice = c.left_label then Avg(cast(c.seqn as float)) else null end...

yes both left join will give me two different results

he explained the same reason i mentioed above that

we are using same tables twice and only differnce is on clause in #tmpcol

Chander ,

frankly i have got you requirement clearly but if you want to know about dynamic sql building and iteration with WHILE loop here is the simple example .

declare @counter...

my senior DBA suggest that

we should avoid this left join

table schema

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

declare @tempRetutnTable table

(

inner_qstn_text nvarchar(max),

leftlabel_avg float,

rightlabel_avg float,

left_label nvarchar(200),

right_label nvarchar(200)

)

CREATE TABLE #coltmp

(

seqn int identity(1,1),

col_choice nvarchar(4000),

left_label nvarchar(200),

right_label...

but why count(*) wll be treated differently from count(id1) though both contains same records?

yes vyas , i dropped the index from acct_id and cont_stub

and now query used "clustered index scan"

but i couldnt understand why it went for clustered index scan.?