[gymratz]

Code I have so far:

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)
Active_Ongoing
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,

[ajwsurfer]

If I am not mistaken, this is a question that belongs in the "Databases" form.

[gymratz]

Not sure, I'm accessing it using ASP.net and VB.net. One of the moderators moved my last similar question to this forum.

[Jayman]

Moved to Database.

Regardless of the language you are programming in, this and the other question are both related to SQL syntax. Therefore they belong in the Database forum.

I moved the other topic into this forum as well.

[gymratz]

Thanks, I'll use this forum from now on :0).
Anyway I'm close!
However my count function is not returning what I want.
What it’s doing is for any client that has an engagement that matches my criteria, it’s counting ALL of the engagements.
For instance if I have 2 clients, 1 of them has 4 engagements, 1 of which matches the criteria, the count returns 4. (Should return 1)
If I set the second client to have 5 engagements, 1 of which matches the criteria, the count returns 9. (Should return 2)
I thought my Distinct in the left join would prevent that....

Select Distinct
	EmpMe.Status as MyStatus,
	EmpMe.Full_Name as TheName,
	NULL as Total_Clients,
	count(Active_O.Client_ID) 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
	LEFT OUTER JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NULL
		) Active_O on C.ID = Active_O.Client_ID
WHERE EmpMe.Status = 'Partner'
Group By EmpMe.Status, EmpMe.Full_Name
Order By TheName

[Trogdor]

You should try to work without distinct, it might not do what you expect.
Actually, if you think you need a distinct, you probably have a problem in a join and are getting duplicates because of it.

Why is the second inner join with a subquery? A normal duo of joins on Rep_Client and Employees would work fine (better)
If you need particular parts of a join to be null, do not use a inner join but outer join and a "where x.id is null" in the whereclause.

The same can be done with the second subquery-join.

I would suggest you first cut out the group and count, and test the results by viewing the actual output list before counting and grouping etc. That way it is much more easy to spot why things behave different then you expect.

Good luck!

[gymratz]

I've changed the INNER JOIN that you mentioned to having two seperate INNER JOIN's instead of the subquery.
So far same result (that is good though, at least it didn't mess anything up).
Thank you.

I do not know of a way to NOT use distinct.
This is what I am trying to accomplish.
For any client that has one or more engagements that meet a criteria, count one for them.
So let's say I have 100 engagements and 40 of them meet the criteria. I don't want it to return a count of 40, I want it to return the number of Clients. Thus if I do distinct client_id, it should return (for instance) 5.

When i run the subquery:

select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NULL

That works great and returns the number I expect.

That query within my main one (now looks like this):

Select
	EmpMe.Status as MyStatus,
	EmpMe.Full_Name as TheName,
	NULL as Total_Clients,
	count(Active_O.Client_ID) as Active_Ongoing,
	count(InActive_O.Client_ID) 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 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
	LEFT JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NULL
		) As Active_O on C.ID = Active_O.Client_ID and RC.Client_ID = Active_O.Client_ID and E.Client_ID = Active_O.Client_ID
	LEFT JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NOT NULL
		) As InActive_O on C.ID = InActive_O.Client_ID
WHERE EmpMe.Status = 'Partner' and RC.Eng_ID is Null
Group By EmpMe.Status, EmpMe.Full_Name
Order By TheName

That returns the number of engagements. I've seen this happen before and so far it's always been with a goof in the join (missing a "something.id = somethingelse.id") But as you may notice I've thrown in a bunch of extra of those, and I'm still getting the same improper result...

[gymratz]

Ok I (think) I got it using Distinct within the Count function!

Select
	EmpMe.Status as MyStatus,
	EmpMe.Full_Name as TheName,
	NULL as Total_Clients,
	count(distinct Active_O.Client_ID) as Active_Ongoing,
	count(distinct InActive_O.Client_ID) 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 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
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NULL
		) As Active_O on C.ID = Active_O.Client_ID
	Left JOIN (
			select Distinct E.Client_ID 
				from Engagements E 
					WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NOT NULL
		) As InActive_O on C.ID = InActive_O.Client_ID
WHERE EmpMe.Status = 'Partner' and RC.Eng_ID is Null
Group By EmpMe.Status, EmpMe.Full_Name
Order By TheName