Join 307,161 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,536 people online right now. Registration is fast and FREE... Join Now!
Below I have a query that took a long time to complete when I had only 100 entries. Now that I have thousands of records it doesn't complete after 3 minutes (time out period).
Can you please help me look at the code and figure out areas that I could utilize views to make this more efficient?
CODE
Select EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName, count(distinct Total_All.Client_ID) as Total_Clients, count(distinct Active_O.Client_ID) as Active_Ongoing, count(distinct InActive_O.Client_ID) as Inactive_Ongoing, count(distinct Total_O.Client_ID) as Total_Ongoing, count(distinct Active_R.Client_ID) as Active_Retros, count(distinct InActive_R.Client_ID) as Inactive_Retros, count(distinct Total_R.Client_ID) as Total_Retros, count(distinct Total_FW_Comps.Composite_ID) as Total_FW_Comp, count(distinct Total_PE_Comps.Composite_ID) as Total_PE_Comp, count(distinct Ongoing_FW_Comps.Composite_ID) as Ongoing_FW_Comp, count(distinct Ongoing_PE_Comps.Composite_ID) as Ongoing_PE_Comp, count(distinct Retro_FW_Comps.Composite_ID) as Retro_FW_Comp, count(distinct Retro_PE_Comps.Composite_ID) as Retro_PE_Comp 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 Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null and E.Classification = 'Verification (Initial)' ) as Retro_FW_Comps on C.ID = Retro_FW_Comps.Client_ID LEFT JOIN ( select distinct Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null and E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True' ) as Retro_PE_Comps on C.ID = Retro_PE_Comps.Client_ID LEFT JOIN ( select distinct Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null AND Comp.Perf_Exam = 'True' ) as Total_PE_Comps on C.ID = Total_PE_Comps.Client_ID LEFT JOIN ( select distinct Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)' ) as Ongoing_FW_Comps on C.ID = Ongoing_FW_Comps.Client_ID LEFT JOIN ( select distinct Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True' ) as Ongoing_PE_Comps on C.ID = Ongoing_PE_Comps.Client_ID LEFT JOIN ( select distinct Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null ) as Total_FW_Comps on C.ID = Total_FW_Comps.Client_ID Left JOIN ( select Distinct E.Client_ID from Engagements E WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Ongoing)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (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)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (Ongoing)') AND E.Inactive IS NOT NULL ) As InActive_O on C.ID = InActive_O.Client_ID Left JOIN ( select Distinct E.Client_ID from Engagements E WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)') AND E.Inactive IS NULL ) As Active_R on C.ID = Active_R.Client_ID Left JOIN ( select Distinct E.Client_ID from Engagements E WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)') AND E.Inactive IS NOT NULL ) As InActive_R on C.ID = InActive_R.Client_ID Left JOIN ( select Distinct E.Client_ID from Engagements E WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)') ) As Total_R on C.ID = Total_R.Client_ID Left JOIN ( select Distinct E.Client_ID from Engagements E WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Ongoing)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (Ongoing)') ) As Total_O on C.ID = Total_O.Client_ID Left JOIN ( select Distinct E.Client_ID from Engagements E WHERE E.Reports_Out is NULL ) As Total_All on C.ID = Total_All.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 Order By MyStatus, TheName
Well, here's how I'd do half of it. See if this gives you any ideas.
CODE
select Composite_ID, Comp.Client_ID, sum(case when E.Reports_Out is Null and E.Classification = 'Verification (Initial)' then 1 else 0 end) as Retro_FW_Comps, sum(case when E.Reports_Out is Null and E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Retro_PE_Comp, sum(case when E.Reports_Out is Null AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Total_PE_Comps, sum(case when E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)' then 1 else 0 end) as Ongoing_FW_Comps, sum(case when E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Ongoing_PE_Comps, sum(case when E.Reports_Out is Null then 1 else 0 end) as Total_FW_Comps from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID
Edit, obviously you can do an optimization by putting E.Reports_Out is Null is the base where and taking it out of the cases, but I wanted to leave it as similar as possible.
This post has been edited by baavgai: 2 Dec, 2008 - 04:24 PM
I just let this run for 25 minuts and got no result. I'm thinking it's either created some sort of loop or is causing an error. It DID work before - with no changes to the code. However, at that time, there was only perhaps 10 clients with a total of 50-100 "Engagements."
There are now 270 clients, 900 engagements, and this will easily quadruple by the end of the week.
Oops, forgot the group by. That last one shouldn't have run at all
CODE
select Composite_ID, Comp.Client_ID, sum(case when E.Classification = 'Verification (Initial)' then 1 else 0 end) as Retro_FW_Comps, sum(case when E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Retro_PE_Comp, sum(case when Comp.Perf_Exam = 'True' then 1 else 0 end) as Total_PE_Comps, sum(case when E.Classification = 'Verification (Ongoing)' then 1 else 0 end) as Ongoing_FW_Comps, sum(case when E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Ongoing_PE_Comps, count(*) as Total_FW_Comps from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID where E.Reports_Out is Null group by Composite_ID, Comp.Client_ID,
Edit, fixed the where.
This post has been edited by baavgai: 2 Dec, 2008 - 04:42 PM
I think that will help me get on the right track. I need to double check all of my logic first - I used what you gave me and I'm getting numbers that aren't making any sense - numbers in the thousands that should be in the 10's. Once I check my logic to make sure I'm thinking this through correctly then I'll continue on with your logic.
Thanks again,
Just noticed your last post - you are right I had to edit it a little bit adding the Grouping before it would run. Luckily SQL throws errors that are sometimes intelligable and let me know what was missing .
This post has been edited by gymratz: 2 Dec, 2008 - 04:51 PM
When you are getting strange numbers, usually it is the distinct that is at fault. See if you can get rid of it completely. Also, be careful with NULL values, especially if they are present in fields you join on. Check if your data contains NULLs in Comp.Client_ID or Engagements.Client_ID
Ok so I started working on this - but my problem is I do not know how to get the results I need WITHOUT using Distinct.
Here is my code:
CODE
select EmpMe.Status, EmpMe.Full_Name, sum(case when E.Reports_Out is NULL then 1 else 0 end) as Total_Clients, sum(case when E.Classification like '%Ongoing%' AND E.Inactive is NULL then 1 else 0 end) as Active_Ongoing, sum(case when E.Classification like '%Ongoing%' AND E.Inactive is not NULL then 1 else 0 end) as InActive_Ongoing, sum(case when E.Classification = 'Verification (Initial)' then 1 else 0 end) as Retro_FW_Comps, sum(case when E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Retro_PE_Comp, sum(case when Comp.Perf_Exam = 'True' then 1 else 0 end) as Total_PE_Comps, sum(case when E.Classification = 'Verification (Ongoing)' then 1 else 0 end) as Ongoing_FW_Comps, sum(case when E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Ongoing_PE_Comps, count(*) as Total_FW_Comps from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID INNER JOIN Clients as C on C.ID = Comp.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 where E.Reports_Out is Null AND RC.Eng_ID is Null AND (EmpMe.Status = 'Partner' or EmpMe.Status = 'Sr. Manager' or EmpMe.Status = 'Manager' or EmpMe.Status = 'Lead Verifier') group by EmpMe.Status, EmpMe.Full_Name Order By EmpMe.Status, EmpMe.Full_Name
The problem is that each client may have 10-100 Engagements - If any ONE of those engagements match the criteria I need to count the client as a whole as just one. Thus I use distinct previously...
Now that I have gotten rid of distinct - I'm getting numbers like "809" that should be closer to 60...
Can I get a slightly harder shove? (In the right direction that is)
I took another stab at it and got the intended RESULTS but it is still slow... So far I only have about half of the query - I haven't done the other half because I'm not sure if this is the best way...
It took about 6 seconds to complete so far...
CODE
Select EmpMe.Status, EmpMe.Full_Name, Count(distinct Counts.Client_ID) as Total_Clients, Count(distinct Counts.Active_Ongoing) as Active_Ongoing, Count(distinct Counts.Inactive_Ongoing) as Inactive_Ongoing, Count(distinct Counts.Retro_FW_Comps) as Retro_FW_Comps, Count(distinct Counts.Retro_PE_Comp) as Retro_PE_Comp, Count(distinct Counts.Total_PE_Comps) as Total_PE_Comps, Count(distinct Counts.Ongoing_FW_Comps) as Ongoing_FW_Comps, Count(distinct Counts.Ongoing_PE_Comps) as Ongoing_PE_Comps 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 Composite_ID, Comp.Client_ID, sum(case when E.Reports_Out is NULL then 1 else 0 end) as Total_Clients, sum(case when E.Classification like '%Ongoing%' AND E.Inactive is NULL then 1 else 0 end) as Active_Ongoing, sum(case when E.Classification like '%Ongoing%' AND E.Inactive is not NULL then 1 else 0 end) as InActive_Ongoing, sum(case when E.Classification = 'Verification (Initial)' then 1 else 0 end) as Retro_FW_Comps, sum(case when E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Retro_PE_Comp, sum(case when Comp.Perf_Exam = 'True' then 1 else 0 end) as Total_PE_Comps, sum(case when E.Classification = 'Verification (Ongoing)' then 1 else 0 end) as Ongoing_FW_Comps, sum(case when E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True' then 1 else 0 end) as Ongoing_PE_Comps from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID where E.Reports_Out is Null Group By Composite_ID, Comp.Client_ID ) as Counts on C.ID = Counts.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 Order By EmpMe.Status, EmpMe.Full_Name
I take that back - what I posted before wasn't right at all... I think it's obvious I did it at night while I was getting tired.
I can not think of any way to get my intended results without using the initial query up top that was scoffed at .
To make it clear what I need, I need: To count how many CLIENTS have one or more Engagements meeting a particular criteria. I then repeat this multiple times with different criteria.
Sorry, I can only work with what I see. Maybe if we take a walk through the analytical process.
Here's your core query:
CODE
select EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName 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 and RC.Eng_ID is Null INNER JOIN Employees as EmpMe on EmpMe.ID = RC.Emp_ID and EmpMe.Status in ('Partner','Sr. Manager','Manager','Lead Verifier') Group By EmpMe.Status, EmpMe.Full_Name
Not knowing what's with all the joins, it's hard to say to point of all the extra tables. Hopefully they're limitiers. What we know for sure it that the results should not exceed what we'd get with this:
CODE
select EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName FROM Employees as EmpMe WHERE EmpMe.Status in ('Partner','Sr. Manager','Manager','Lead Verifier') Group By EmpMe.Status, EmpMe.Full_Name
Reasonably, we'd want to boil all sub queries down to where they give us an Employee ID as a key.
QUOTE(gymratz @ 5 Dec, 2008 - 01:03 PM)
To count how many CLIENTS have one or more Engagements meeting a particular criteria.
Interesting. The base query doesn't represent this particularly well.
So, for this:
CODE
Left JOIN ( select Distinct E.Client_ID from Engagements E WHERE E.Reports_Out is NULL ) As Total_All on C.ID = Total_All.Client_ID
Did you perhaps really want something like this:
CODE
Left JOIN ( select RC.Emp_ID, count(distinct E.Client_ID) as TotalClients from Engagements E INNER JOIN Rep_Client as RC on RC.Client_ID = E.Client_ID WHERE E.Reports_Out is NULL group by RC.Emp_ID ) As Total_All on Total_All.Emp_ID = EmpMe.ID
For something like:
CODE
select EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName, EnClient.Total_Clients, FROM Employees as EmpMe Left JOIN ( select RC.Emp_ID, count(distinct E.Client_ID) as Total_Clients from Engagements E INNER JOIN Rep_Client as RC on RC.Client_ID = E.Client_ID WHERE E.Reports_Out is NULL group by RC.Emp_ID ) As EnClient on EnClient.Emp_ID = EmpMe.ID WHERE EmpMe.Status in ('Partner','Sr. Manager','Manager','Lead Verifier')
Flesh it out a little with some more criteria:
CODE
select EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName, EnClient.Total_Clients, EnClient.Total_O as Total_Ongoing FROM Employees as EmpMe Left JOIN ( select RC.Emp_ID, count(distinct E.Client_ID) as Total_Clients, sum(case when E.Classification in ('Verification (Ongoing)','Model Exam (Ongoing)','Strategy Exam (Ongoing)','AUP (Ongoing)') then 1 else 0 end) as Total_Ongoing from Engagements E INNER JOIN Rep_Client as RC on RC.Client_ID = E.Client_ID WHERE E.Reports_Out is NULL group by RC.Emp_ID ) As EnClient on EnClient.Emp_ID = EmpMe.ID WHERE EmpMe.Status in ('Partner','Sr. Manager','Manager','Lead Verifier')
Wait, you want them distinct client per employee? You can do it at the case level.
CODE
select EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName, EnClient.Total_Clients, EnClient.Total_O as Total_Ongoing FROM Employees as EmpMe Left JOIN ( select RC.Emp_ID, count(distinct E.Client_ID) as Total_Clients, count( distinct (case when E.Classification in ('Verification (Ongoing)','Model Exam (Ongoing)','Strategy Exam (Ongoing)','AUP (Ongoing)') then E.Client_ID else null end)) as Total_Ongoing from Engagements E INNER JOIN Rep_Client as RC on RC.Client_ID = E.Client_ID WHERE E.Reports_Out is NULL group by RC.Emp_ID ) As EnClient on EnClient.Emp_ID = EmpMe.ID WHERE EmpMe.Status in ('Partner','Sr. Manager','Manager','Lead Verifier')
Hope this helps.
This post has been edited by baavgai: 5 Dec, 2008 - 12:19 PM
Wow that will take some reading but was very thorough - thank you.
In the mean time here is what I did. I broke up my queries into two separate ones for now. The first half counts how many clients a person is working on that meet different criteria.
CODE
Select EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName, count(distinct Total_O.Client_ID) + count(distinct Total_R.Client_ID) as Total_Clients, count(distinct Active_O.Client_ID) as Active_Ongoing, count(distinct InActive_O.Client_ID) as Inactive_Ongoing, count(distinct Total_O.Client_ID) as Total_Ongoing, count(distinct Active_R.Client_ID) as Active_Retros, count(distinct InActive_R.Client_ID) as Inactive_Retros, count(distinct Total_R.Client_ID) 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)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (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)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (Ongoing)') AND E.Inactive IS NOT NULL ) As InActive_O on C.ID = InActive_O.Client_ID Left JOIN ( select Distinct E.Client_ID from Engagements E WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Ongoing)' or E.Classification = 'Model Exam (Ongoing)' or E.Classification = 'Strategy Exam (Ongoing)' or E.Classification = 'AUP (Ongoing)') ) As Total_O on C.ID = Total_O.Client_ID Left JOIN ( select Distinct E.Client_ID from Engagements E WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)') AND E.Inactive IS NULL ) As Active_R on C.ID = Active_R.Client_ID Left JOIN ( select Distinct E.Client_ID from Engagements E WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)') AND E.Inactive IS NOT NULL ) As InActive_R on C.ID = InActive_R.Client_ID Left JOIN ( select Distinct E.Client_ID from Engagements E WHERE E.Reports_Out is NULL and (E.Classification = 'Verification (Initial)' or E.Classification = 'Model Exam (Initial)' or E.Classification = 'Strategy Exam (Initial)' or E.Classification = 'AUP (Initial)') ) As Total_R on C.ID = Total_R.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 Order By MyStatus, TheName
To try to explain how my tables are laid out: Clients (No data is being pulled from this table, but I note it because it is the root of Client_ID). Employees - The name and the status of the employee are being pulled from this table - has an Employee ID as a PK. Engagements - this has information such as classifications, and the Client_ID to know which client an engagement belongs to. Rep_Client - This table ties together the engagement and the employee since multiple employees can work on the same engagement, and one employee works on multiple engagements.
A client can have (and will) multiple Engagements - but an engagement only belongs to one Client.
An example may be as such. 5 Clients with a total of 100 engagements between the 5 of them. An employee may be on 3 engagements for the first client, 10 engagements for the second client, and none for the other three. Out of the first 3 engagments, two may be ongoing one retro. Out of the second clients 10 engagements, 1 may be retro 9 may be ongoing. For total retro it would show 2 clients. For total ongoing it would show 2 clients. For "total" it would show 4 (now being calculated by total retro + total ongoing).
All that being said - this part of the query runs in under a second currently.
Below is the second part of the query (right now broken out to be it's own entire query, although I'd like to combine it with the first one once I get it running. This is the part I narrowed down to taking forever. What is strange is, any SINGLE part of the query (any one of the joins) only takes about 15 seconds. But the time it takes to execute as I add them together seems to go up exponentially.
CODE
Select EmpMe.Status as MyStatus, EmpMe.Full_Name as TheName, count(distinct Total_PE_Comps.Composite_ID) as Total_PE_Comp, count(distinct Ongoing_FW_Comps.Composite_ID) as Ongoing_FW_Comp, count(distinct Ongoing_PE_Comps.Composite_ID) as Ongoing_PE_Comp, count(distinct Retro_FW_Comps.Composite_ID) as Retro_FW_Comp, count(distinct Retro_PE_Comps.Composite_ID) as Retro_PE_Comp 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 Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null and E.Classification = 'Verification (Initial)' ) as Retro_FW_Comps on C.ID = Retro_FW_Comps.Client_ID LEFT JOIN ( select distinct Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null and E.Classification = 'Verification (Initial)' AND Comp.Perf_Exam = 'True' ) as Retro_PE_Comps on C.ID = Retro_PE_Comps.Client_ID LEFT JOIN ( select distinct Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null AND Comp.Perf_Exam = 'True' ) as Total_PE_Comps on C.ID = Total_PE_Comps.Client_ID LEFT JOIN ( select distinct Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)' ) as Ongoing_FW_Comps on C.ID = Ongoing_FW_Comps.Client_ID LEFT JOIN ( select distinct Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null and E.Classification = 'Verification (Ongoing)' AND Comp.Perf_Exam = 'True' ) as Ongoing_PE_Comps on C.ID = Ongoing_PE_Comps.Client_ID LEFT JOIN ( select distinct Composite_ID, Comp.Client_ID from Composites Comp INNER JOIN Engagements as E on E.Client_ID = Comp.Client_ID WHERE E.Reports_Out is Null ) as Total_FW_Comps on C.ID = Total_FW_Comps.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 Order By MyStatus, TheName
I'll look through what you posted though to see if I can figure this part out. Also - as a final note. Composites table is linked using Client_ID to the Clients table. A client will have multiple Composites - but a Composite only belongs to one client.
I haven't got the whole thing working yet; however, one of your ideas has helped a lot and got the ball rolling.
CODE
count( distinct (case when E.Classification in ('Verification (Ongoing)','Model Exam (Ongoing)','Strategy Exam (Ongoing)','AUP (Ongoing)') then E.Client_ID else null end)) as Total_Ongoing
Forum Index:
Programming Help |
C and C++ |
VB6 |
Java |
VB.NET |
C# |
ASP.NET |
PHP |
ColdFusion |
Perl and Python |
Ruby |
Databases |
Other Languages |
Game Programming |
Mobile Development |
Software Development |
Computer Science |
Industry News |
Programming Tutorials |
C++ Tutorials |
Visual Basic Tutorials |
Java Tutorials |
VB.NET Tutorials |
C# Tutorials |
Linux Tutorials |
PHP Tutorials |
ColdFusion Tutorials |
Windows Tutorials |
HTML/JavaScript Tutorials |
CSS Tutorials |
Flash Tutorials |
Web Promotion Tutorials |
Graphic Design & Photoshop Tutorials |
Software Development Tutorials |
Database Tutorials |
Other Language Tutorials |
ASP.NET Tutorials |
Game Programming Tutorials |
WPF & Silverlight Tutorials |
.
| 
