Need help for my SQL query

Question

Well I may have posted this earlier also, but unable to find the answer so far, so please help me on this one.

My database structure:

ATT (Activity table)

• Act_ID(PK)
• Assigned_To_ID (FK, refer to Emp_ID)
• Project_ID (FK, refer to Project_ID)
• Product_ID (FK, refer to Product_ID)
• Status (can be New, OnHold, Cancel, Closed)

Product_Table

• Product_ID (PK)
• Product_Name

Project_Table

• Project_ID (PK)
• Project_Name

Employee_Table

• Emp_ID (PK)
• F_Name.

Constraints

• In 1 Project --> Multiple employees can work
• 1 Employee -- > Can be assigned multiple activities (Act_ID)
• At any given point of time Status can be any of those given values

Now in my SQL query what I want to do is a load check ::

An activity (represented by Act_ID) is assigned to an employee is represented by Assigned_To_ID, for Project (Project_ID in ATT_Table) and a particular status. I need to output 5 values from my SQL query:

1. Name of Employee
2. Name of Project
3. Number of total activities an Employee(In A) is assigned.
4. Number of activities an Employee(In A) is assigned where Status is either New or InProcess.
5. Load. That will be = C/D * 100 (C is a larger value than D, which is obvious.)

Now There may be a condition That an employee let's say E1 is working on project P1,P2. So my table output will be look like this::

A    B     C    D     E
E1   P1    
E1   P2 

So here C, D, E will be having values corresponding to activities for that Project(P1 or P2)

I have tried this so far ::

SELECT 
    F_Name AS A, 
    Project_Name AS B, 
    Count(Act_ID) AS C, 
    Count(Act_ID) AS D
FROM 
    Employee_Table, ATT_Table, Project_Table
WHERE 
    ATT_table.[Assigned_To_ID] = Employee_Table.[Emp_ID] 
    AND Project_Table.[Project_ID] = ATT_Table.[Project_ID] 
    AND Count(Act_ID) IN (SELECT Count(Act_ID)
                          FROM ATT_Table
                          WHERE ATT_Table.[Status] <> 'Closed' 
                            AND ATT_Table.[Status] <> 'OnHold')
GROUP BY 
     F_Name, Project_Name;

I am getting A, B, C. But when I try to find the activities for status check I cannot execute this query as it gives me a message cannot write count in WHERE clause. please help me on this one. Thanks..

Answer 1

Maybe like this. try it....

Select Emp.F_Name As A,
  Proj.Project_Name As B,
  Count(ATT.Act_ID) As C,
  sum( IIF( ATT.Status IN ( 'Closed', 'OnHold' ), 0, 1 )) as D,
  (Count(ATT.Act_ID) / sum( IIF( ATT.Status IN ( 'Closed', 'OnHold' ), 0, 1 ))) * 100 as E 
From Employee_Table As Emp Inner Join
  (Product_Table As Prod Inner Join
  (ATT_Table As ATT Inner Join Project_Table As Proj On Proj.Project_ID=ATT.Project_ID) 
  On Prod.Product_ID=ATT.Product_ID) 
  On Emp.Emp_ID=ATT.Assigned_To_ID
Group By Emp.F_Name,Proj.Project_Name

Answer 2

SELECT 
      E1.F_Name AS A, 
      P1.Project_Name AS B, 
      Count(A1.Act_ID) AS C, 
      sum( IF( A1.Status IN ( 'Closed', 'OnHold' ), 0, 1 )) as D,
      (Count(A1.Act_ID) / sum( IIF( A1.Status IN ( 'Closed', 'OnHold' ), 0, 1 ))) * 100 as E
   FROM 
      ATT_Table A1
         JOIN Employee_Table E1
            ON A1.Assigned_To_ID = E1.Emp_ID
         JOIN Project_Table P1
            ON A1.Project_ID = P1.Project_ID
   GROUP BY 
      F_Name, Project_Name;

Answer 3

You mentioned Product_Table, but I don't see how that table is involved in your question. For the rest, I would pull columns A, B, C, and D from a subquery, and compute E in the outer query.

SELECT
    sub.F_Name AS A,
    sub.Project_Name AS B,
    sub.C,
    sub.D,
    ((sub.C / sub.D) * 100) AS E
FROM
    (
        SELECT
            Emp.F_Name,
            Proj.Project_Name,
            Count(att.Act_ID) AS C,
            Sum(IIf(att.Status IN ('New', 'InProcess'), 1, 0)) AS D
        FROM
            (ATT_Table AS att
            INNER JOIN Employee_Table AS Emp
            ON Emp.Emp_ID = att.Assigned_To_ID)
            INNER JOIN Project_Table AS Proj
            ON Proj.Project_ID = att.Project_ID 
        GROUP BY
            Emp.F_Name,
            Proj.Project_Name
    ) AS sub;