如何解决这个问题- [英] how to solve this querry-

问题描述

我有两个表employee和时间表-

员工包含员工姓名，员工姓名
时间表包含-员工身份，开始日期，结束日期，营业时间

用户在同一天输入多个记录.

现在我希望哪些员工的工作时间少于n小时.

假设我想要日期2012年1月3日，花费的时间= 5小时

所以我想让2012年1月3日至少5个小时不工作的员工

结果我想要-
员工姓名，开始日期，工作时间


我正在使用此Querry，它给了employeeid我想要的是employeename而不是employeeid

Hi, i have two tables employee and timesheet-

employee contains-employeeid,employeename
timesheet contains-employeeid,startdate,enddate,hourstaken

user enter multiple records on a same day.

now i want which employees hourstaken is less then n hours.

suppose i want date-01/03/2012 and hourstaken = 5hr

so i want how many employees are not working atleast 5 hr on a 01/03/2012

In a result i want -
employeename,startdate,hourstaken


i am using this querry it gives employeeid i want employeename instead of employeeid

select convert(VARCHAR, '2012/02/18', 111) as startdate, sum(hourstaken) as hourstaken,employeeid
from timesheet where convert(VARCHAR, startdate, 111) = '2012/02/18'
group by employeeid having sum(hourstaken) < '5'

谢谢

Thanks

推荐答案

您好....

如果您已经具有时间表中总小时数"列的值，请尝试使用此方法...

hi there....

if u already have value for the total hours column in your timesheet then try this...

SELECT e.employeeid,e.employeename,t.startdate,SUM(t.hourstaken)
FROM employee e INNER JOIN timesheet t
ON e.employeeid=t.employeeid
GROUP BY e.employeeid,e.employeename,t.startdate
HAVING SUM(t.hourstaken)< 5

希望这对您有帮助...

回复您的评论...

hope this helps...

revert back with ur comments...

alter procedure NoOfEmpWorkingLess @date datetime,@hrsTaken int
AS
select e.employeename,t.startdate,sum(t.hourstaken) from timesheet t
inner join
employee e on e.employeeid=t.employeeid
 where startdate=@date  group by e.employeename,t.startdate
having sum(hourstaken)>@hrsTaken

欢迎回来，
一切顺利.

Welcome back,
All the best.

这篇关于如何解决这个问题-的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！