Select Distinct EmpMe.Status as Status, EmpMe.Full_Name as TheName, NULL as Total_Clients, NULL as Active_Ongoing, NULL as Inactive_Ongoing, NULL as Total_Ongoing, NULL as Active_Retros, NULL as Inactive_Retros, NULL as Total_Retros FROM Engagements as E INNER JOIN Clients as C on C.ID = E.Client_ID INNER JOIN ( select a.Client_ID as ID, b.Full_Name, B.Status, a.Position, a.eng_id from Rep_Client a inner join Employees b on b.ID = a.Emp_ID ) EmpMe on C.ID = EmpMe.ID and EmpMe.eng_id is null WHERE EmpMe.Status = 'Partner' Order By TheName
The Where EmpMe.Status = ‘Partner’ will be replaced with @status and passed in by the webpage.
What this gives me are the people’s names, and a list of Null’s.
I need to fill in each NULL with the following (Example of one below, if I can get help with that I can do the rest I think)
Count of the number of clients that have 1 or more engagements with the follow attributes:
E.Reports_Out is null
E.Classification = ‘Verification (Ongoing)’
E.Inactive is null
Now obviously this should only count the clients that the employee is assigned to, but I THINK I have that taken care of in my second INNER JOIN.
It does not matter if one client has 1, or 10 engagements that match those criteria it should still only return as 1 additional count, as the point is to count the clients that have 1 or more of such engagements.
Thanks in advance,