基于多行的时间计算
基于多行的时间计算
提问于 2017-01-26 03:23:13
我正在尝试找出打卡/打卡时间是否与2016年他们为每个员工工作的总次数相匹配。
SELECT CONVERT(varchar, PUNCH, 103), JOBDATE, EMPLOYEE, JOB, HOURS
FROM JOBTABLE
WHERE JOBDATE = convert(int, convert(varchar(10), getdate(), 112))
AND EMPLOYEE = 105表：
PUNCH JOBDATE EMPLOYEE JOB HOURS
0600 20170123 105 AA785 7
1024 20170123 105 AA258 0.5
0600 20170123 105 LOGIN 0
1558 20170123 105 LOGIN 0在这一天的示例中，员工105在600打卡上班，在1558 (下午3:58)打卡下班。该字段是一个字符。除了关键字段之外，没有办法真正区分打卡输入和打卡输出。打卡输入是一个比打卡输出更小的数字，没有其他的韵律或原因。
所以这个人工作了大约10个小时。然而，如果你把他花在工作上的时间加起来，加起来就是7.5。我需要知道这其中的区别。如果他的总工作时间少于他的拳头，那就是一个大问题。我需要知道这一年的总数。因此，在105个小时内，他少了2.5小时。
我不知道如何计算工作时间，因为它们是两个独立的行。然后每天检查每个员工的每一行。有人能帮上忙吗？非常感谢！
employee jobdate sumHours DiffHours ScheduledHours
105 20170123 7.5 -2.466666 10
105 20170124 1.5 -6.5 8更多测试数据
Job Hours Punch
123456X 0.98 0301 20160412
123451 1.75 0000 20160412
123452 1.27 0145 20160412
LOGIN 0 2345 20160412
LOGIN 0 0346 20160412
123453 0.25 2345 20160412所以看起来他是第三班工作，但他是第一个工作的。我认为必须加上这一点。谢天谢地，这里有一个移位栏。无论如何，他工作了4.25个小时，这是正确的，但它说他的计划是20。minPunch和maxPunch是226和1425，它们应该是346和2345。
回答 2
Stack Overflow用户
发布于 2017-01-26 03:57:29
您需要连接两行匹配date和employee的数据，而不是date；因此，您需要根据表本身来连接表：
SELECT CONVERT(varchar, A.PUNCH, 103) AS PUNCH_A, CONVERT(varchar, B.PUNCH, 103) AS PUNCH_B, A.JOBDATE, A.EMPLOYEE, A.JOB, A.HOURS + B.HOURS as TOTALHOURS
FROM JOBTABLE A
INNER JOIN JOBTABLE B
ON A.JOBDATE = B.JOBDATE AND A.EMPLOYEE=B.EMPLOYEE AND A.JOB=B.JOB
AND A.JOB <> 'LOGIN'
AND A.PUNCH<B.PUNCH -- this is what makes sure A is the punch in and B punch out
WHERE A.JOBDATE = convert(int, convert(varchar(10), getdate(), 112))
AND A.EMPLOYEE = 105Stack Overflow用户
发布于 2017-01-26 05:13:02
您可以将打孔机转换为分钟，并将小时转换为分钟。从那里你可以检查小时数是否小于(maxpunch minpunch)
这可能会让你走上正确的方向：
rextester：http://rextester.com/ZRXH77147
create table t (
punch char(4)
, jobdate char(8)
, employee int
, job char(5)
, hours decimal(5,2)
);
insert into t values
('0600','20170123',105,'AA785',7)
,('1024','20170123',105,'AA258',0.5)
,('0600','20170123',105,'LOGIN',0)
,('1558','20170123',105,'LOGIN',0);查询：
with cte as (
select
employee
, jobdate
, minPunch = min(case
when job = 'login'
then (convert(int,left(Punch,2))*60.0)
+convert(int,right(Punch,2))
else null
end
)
, maxPunch = max(case
when job = 'login'
then (convert(int,left(Punch,2))*60.0)
+convert(int,right(Punch,2))
else null
end
)
, sumHours = sum(hours)
from t
group by
employee
, jobdate
)
select
Employee
, jobdate
, SumHours
, diffHours = convert(decimal(9,2)
,round(((maxPunch-minPunch)-(sumHours*60.0))/60.0,1)
)
, ScheduledHours = convert(decimal(9,2),round((maxPunch-minPunch)/60.0,1))
from cte 返回：
+----------+----------+----------+-----------+----------------+
| Employee | jobdate | SumHours | diffHours | ScheduledHours |
+----------+----------+----------+-----------+----------------+
| 105 | 20170123 | 7,50 | -2,50 | 10,00 |
+----------+----------+----------+-----------+----------------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41859940
复制相关文章
相似问题
腾讯云开发者
