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屋!