I simplified the View, alone it now returns exactly what I expect it to.
For each client, for each unique Classification they have, it returns the Max YearQuarter (which is a concatenation of Year and Quarter).
Problem is, when I try to pull from that View in my main query, it totally messes stuff up.
View:
CODE
Select
Client_ID,
Classification,
MAX(CAST(YEAR(End_Year)as varchar(50)) + End_Quarter) as YearQuarter
FROM Engagements as E
WHERE Client_ID is not NULL and Classification is not NULL and CAST(YEAR(End_Year)as varchar(50)) + End_Quarter is not NULL and Classification like '%Ongoing%'
GROUP BY Client_ID, Classification
Order By YearQuarter
Main Query:
This is pulling items that is has no right pulling. It shows, for instance, an item called “Verification (Initial)” yet the view doesn’t have this at all! The numbers it is pulling also seems to be off (higher by at least one). I think I'm missing something that is causing it to pull from areas it isn't supposed to. Perhaps something in my join?
CODE
Select
EmpMe.Status as MyStatus,
EmpMe.Full_Name as TheName,
E.Classification as Classification,
Count(distinct case when OA.YearQuarter = '20073Q' then 1 else 0 end) as [3Q07],
Count(distinct case when OA.YearQuarter = '20072Q' then 1 else 0 end) as [2Q07],
Count(distinct case when OA.YearQuarter = '20071Q' then 1 else 0 end) as [1Q07]
From Engagements as E
INNER JOIN Clients as C on C.ID = E.Client_ID
INNER JOIN Rep_Client as RC on RC.Client_ID = C.ID and RC.Client_ID = E.Client_ID
INNER JOIN Employees as EmpMe on EmpMe.ID = RC.Emp_ID
INNER JOIN [Ongoing_A] as OA on C.ID = OA.Client_ID
WHERE (EmpMe.Status = 'Partner' or EmpMe.Status = 'Sr. Manager' or EmpMe.Status = 'Manager' or EmpMe.Status = 'Lead Verifier') and RC.Eng_ID is Null
Group By
EmpMe.Status,
EmpMe.Full_Name,
E.Classification
This post has been edited by gymratz: 10 Oct, 2008 - 02:02 PM
| 