Sql查询执行对Employee的负载检查 [英] Sql query to perform Load check for an Employee

问题描述

我有这样的表设计...

• 1个项目 - >多个产品。


• 1产品 - >多环境。


• 1个员工 - >多个活动。

状态可以=new inprogress，on hold，closed

 活动表 -  ActID（PK），assignedtoID（FK），Env_ID （FK），Product_ID（FK），project_ID（FK），状态。 
 Product_table  -  Product_ID（PK），Product_name 
 Project_Table- Project_ID（PK），Project_Name。 
 Environment_Table- Env_ID（PK），Env_Name。 
 Employee_Table- Employee_ID（PK），Name。 
 Employee_Product_projectMapping_Table -Emp_ID（FK），Project_ID（FK），Product_ID（FK）。 
 Product_EnvMapping_Table  -  Product_ID（FK），Env_ID（FK）。 
  

加载检查查询

首先查找分配给特定项目中每个员工的活动总数。

（A）。然后查找该项目中该员工的活动数，其中status = new或inprogress。
（B）。分割A / B。以百分比值计算此A / B。

所以我的最终查询表输出将包括

• / li>
  
• 项目名称


• 总分配行为（计数值）


• 计数）


• 载入（​​百分比值）

任何人都可以帮助我并解释我可以如何实现这个结果？谢谢。

到目前为止，我已经尝试了这么多...获取A和B的结果，但现在我需要结合这两个...

 （A）。 
 
 SELECT activity_table.assignid，activity_table.projid，Count（activity_table.actid）AS CountOfactid 
 FROM activity_table 
 GROUP BY activity_table.assignid，activity_table.projid; 
 
（B）。 
 
 SELECT activity_table.assignid，activity_table.projid，Count（activity_table.actid）AS CountOfactid 
 FROM activity_table where（'new'，'inprogress'）
 GROUP BY activity_table .assignid，activity_table.projid; 
  

解决方案

这是Upto 3rd result ... not for 4th一个。

 选择Employee_table.name，Project_table.Project_Name，Count（activity_table.actid）AS CountOfactid 
 
从活动表，Employee_Table，Project_Table，Employee_Product_projectMapping_Table 
 
其中Activity_Table.Product_Id = Project_Table.Product_Id 
 
和
 
 aage ka语法yaad nai aa raha hai ... 
 
 Employee_Product_projectMapping_Table aur Employee_Table ko通过等于产品id ... 
组合karke emp名称 

为第四个使用嵌套查询......

会尝试解决这个查询asap .....：P

409008 ....

I have a table design like this ...

• 1 Project--> Multiple products.
• 1 Product--> Multiple environment.
• 1 Employee--> Multiple activities.

Status can be = "new", "inprogress", "on hold", "closed"

    Activity table- ActID(PK), assignedtoID(FK), Env_ID(FK), Product_ID(FK), project_ID(FK), Status. 
    Product_table - Product_ID(PK), Product_name
    Project_Table- Project_ID(PK), Project_Name. 
    Environment_Table- Env_ID(PK), Env_Name. 
    Employee_Table- Employee_ID(PK), Name.
    Employee_Product_projectMapping_Table -Emp_ID(FK), Project_ID(FK), Product_ID(FK).
    Product_EnvMapping_Table - Product_ID(FK), Env_ID(FK). 

Load check query

First find out the total number of activities assigned to each employee in a particular project.

(A). then find the number of activities for that employee in that project where status = new or inprogress. (B). Divide A/B. Compute this A/B in percentage value.

So my final query table output will consist of

• Name of employee
• name of project
• total assigned act(Count value)
• Act having status new or in process(Count)
• Load(the percentage value. )

Can anyone help me with the query and explain how I can achieve this result? Thanks.

So far i have tried this much ... getting the results of A and B but now i need to combine these two...

(A).

SELECT activity_table.assignid, activity_table.projid, Count(activity_table.actid) AS CountOfactid
FROM activity_table
GROUP BY activity_table.assignid, activity_table.projid;

(B).

SELECT activity_table.assignid, activity_table.projid, Count(activity_table.actid) AS CountOfactid
FROM activity_table where status in ('new','inprogress')
GROUP BY activity_table.assignid, activity_table.projid ;

解决方案

This is Upto 3rd result...not for 4th one.

select Employee_table.name,Project_table.Project_Name, Count(activity_table.actid) AS CountOfactid

from Activity table,Employee_Table,Project_Table,Employee_Product_projectMapping_Table

where Activity_Table.Product_Id=Project_Table.Product_Id

 and 

aage ka syntax yaad nai aa raha hai...

Employee_Product_projectMapping_Table aur  Employee_Table ko combine karke emp name through equating product id...

for the 4th one use nested query......

will try to solve this query asap.....:P

409008....

这篇关于Sql查询执行对Employee的负载检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！