-- check first using the corresponding SELECT:

SELECT a.*, b.*, c.*

from locn a

join acs b on a.[lid]=b.[lid]

join aco c on c.[uid]=b.[uid]

where c.[type]='c'

mic.con87 (12/22/2011)

---

Well Basically I look at Staging.ClaCases and select the NameID for the records your query returned. This will return all the claims that this NameID has.

I focus on the...

and how do the counts compare to the query "apply...

How are you measuring this?

You might get away with the following as a more efficient alternative to the inner select - give...

Good - then apply the next level:

SELECT

d.NameID,

NoOfSOSCalls = COUNT(*)--,

--Reported_SOS = MAX(ISNULL(CASE WHEN d.QuestionID = 202 THEN d.Answer ELSE NULL END,0))

FROM (

SELECT

cc.NameID,

--cqv.QuestionDate,

--it.discoverdate,

cqv.ClaQuestionHistoryID,

MAX_ClaQuestionHistoryID = MAX(cqv.ClaQuestionHistoryID) OVER(PARTITION BY cqv.ClaCaseID,...

They will...

mic.con87 (12/22/2011)

---

I had a look at your code and I found something that ensures no results will be returned ie AND it.QuestionClassID = cqv.QuestionClassID

it.QuestionClassID will never be the same as...

Okaaaay...comment out the join to the temp table, it's the only change left. Also, please post the...

Sure. Focus on the inner SELECT:

SELECT

cc.NameID,

cqv.QuestionID,

cqv.Answer,

cqv.ClaQuestionHistoryID,

MAX_ClaQuestionHistoryID = MAX(cqv.ClaQuestionHistoryID) OVER(PARTITION BY cqv.ClaCaseID, cqv.QuestionID, cqv.QuestionClassID)

FROM Staging.ClaCases cc

INNER JOIN #InitialTable_24 it ON it.NameID = cc.NameID

INNER JOIN StagingViews.ClaQuestionView cqv

ON...

Dev (12/22/2011)

---

ChrisM@home (12/22/2011)

---

Dev (12/21/2011)

---

Title FName LName Degree

Agree on Names but Titles & Degrees shouldn't be in...

mic.con87 (12/22/2011)

---

...when using your code i receive the error: 'The column 'ClaCaseID' was specified multiple times for 'd'.'

I'm not sure what you mean by "Also, can you change the column...

mic.con87 (12/22/2011)

---

...

My problem is I need to add condition: where CTE.QuestionDate < it.discoverdate

Now obtaining CTE.QuestionDate is a problem. When I use this in the Group By clause in the 'FROM...

Hello

The first query in your post shows a LEFT JOIN, but there are references to columns from it in the WHERE clause, resulting in an INNER join.

Written slightly differently, it...

Try telling that...