OK back to the original DDL and general problem. That CASE statment works, however I don't have counts returning for two of those values. This would be fine except I...

Hello Gail!

So, that results in empty cells. I know I should post some ddl, but any ideas?

BTW, the string between the first pair of backslashes will vary in length from row to row

can i substitute the column name for the @parameter?

eg.

,SUBSTRING(RIGHT('DTS.SuitePath',DATALENGTH('DTS.SuitePath') - 1),1,PATINDEX('%\%',RIGHT('DTS.SuitePath',DATALENGTH('DTS.SuitePath') - 1)) - 1)

as 'SuitePath'

yes (thanks again Lynn)

Thanks for both approaches. Went with drew.allen's because I think Lynn's doesn't get the sum total for all test cases ('Passed', 'Failed', 'Blocked', 'Not Executed') as TotalPlanned and alas I...

OK, thank you for the replies. It is good mentorship from high level that helps when with foray into lower level decisions. Balance. Thanks.

capn (or drew), please recommend an approach. I have a weekend and many reports. I was about to invest a lot of time learning how to use CTEs on...

Is my understanding correct, that each subsequent CTE can pull in additional joins for the gradually expanding breadth of the query?

I want to use CTE to avoid...

the reason I believe I need multiple CTEs is because each subsequent CTE will introduce additional table joins which I do not need for the intial data set.

OK. Looks good capn.hector. Please read on.

I need to build a query for a report and it has to be filtered on a number of levels. I...

Look here's what I've tried. I have been reading about CTE and I must be fried but it is not going anywhere for me. spinning my wheels horrifically.

Please point...

capn.hector or somebody, will you please assist me with CTEs some more? I'm having a terrible time of it. I want to select against the CTE.

Please show me how...

SQLKnowItAll. sorry to get off track, but I tried Patrick's CTE and I am finding it much slower then the rowcount. At least in my dw where I have...

Thanks. Yes, I meant MS Excel for reporting.

That assurance is all I need to go ahead and build my queries using CTEs, having faith that when I...