Here's an alternative version which might be faster:

-- inline tally table has 7 rows = "6 months back"

;WITH iTally (n) AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT...

ROW_NUMBER is ideal for this - can you explain why you...

SELECT *

FROM #Table1 t1

CROSS APPLY (

SELECT TheDate = CONVERT(DATE,'01/'+LEFT(t1.Period,2)+'/'+RIGHT(t1.Period,4),103)

) x

OUTER APPLY (

SELECT *

FROM #Table2 t2

WHERE t2.Name = t1.Name

AND CONVERT(DATE,'01/'+LEFT(t2.Period,2)+'/'+RIGHT(t2.Period,4),103)

BETWEEN DATEADD(mm,-6,x.TheDate) AND x.TheDate

) y

Thanks Ron.

Thank you! 🙂

Use the MAXRECURSION hint:

SELECT columns

FROM recursivecte

OPTION (MAXRECURSION 0); -- unlimited

In practice you might want to set it to a sensible value then increment, JIC your CTE loops.

-- check the query

SELECT *, UpdateMe = CASE WHEN a.SummaryType <> '0' THEN 'Y' ELSE 'N' END

FROM Table_A a

OUTER APPLY (

SELECT Result = (SUM(ai.Pass)/NULLIF(SUM(ai.VM_Id)*1.0,0)) * 100

FROM Table_A ai

WHERE...

-- Always run a SELECT first to see which rows are affected.

-- This query should return the rows you want to keep

SELECT MemberId, [Event], [Event Date]

FROM (

SELECT MemberId, [Event], [Event...

-- Since there are no matches on col1 between the two tables,

-- surely the starting point of the results table should reflect this?

t.col1 ...

No worries. I like this better, can we use it? I think folks will find it easier to...

;WITH Sampledata (id, name) AS (

SELECT 1 , 'srinivas ' UNION...

tom.wauters (4/25/2013)

---

@Lynn, indeed, I have read it, I also did a test and concatenated 'Value' with 'e0', and still the IsNumeric evaluates too 1, so it really always is a...

iam777here4u (4/25/2013)

---

That query is having a problem, lets say the records with id value 4 in column mytab1col1 doesn't have any mismatch. The result still displays id 4, which it...

This works too:

WITH CteComplement AS (

SELECT *, CONVERT(numeric(14,2),Value) as Amount

FROM #Test

WHERE PathId IN (30,31)

)

-- Cte to add a flag to dataset, which indicates if the Amount <> 0 (flag =...

Tom, this CTE method does exactly the same thing - it reverses the order in which the two predicates are evaluated. The CTE's aren't evaluated in order, they're more or...