基于多行的时间计算

I am trying to find if the punch in/punch out times match the total number of times they worked for every employee in 2016.

我试图找出打卡/打卡时间是否与他们在2016年为每位员工工作的总次数相匹配。

SELECT CONVERT(varchar, PUNCH, 103), JOBDATE, EMPLOYEE, JOB, HOURS
FROM JOBTABLE
WHERE JOBDATE = convert(int, convert(varchar(10), getdate(), 112))
AND EMPLOYEE = 105

Table:

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

In this example on this day, employee 105 punched in at 600 and punched out at 1558 (3:58PM). That field is a char. There's no way to tell really the difference between a punch in and punch out besides a key field. The punch in is a lower number than the punch out, with no other rhyme or reason.

在这一天的例子中,员工105打了600,并在1558(下午3:58)打了一拳。该字段是一个字符。除了一个关键领域之外,没有办法真正说出打入和打出之间的区别。打卡的数量低于打卡数量,没有其他押韵或理由。

So this person worked about 10 hours. However, if you add up the time he put on to his jobs, it adds up to 7.5. I need to know that difference. If his total hours worked are less than what his punches are, then that's a big problem. I need to know the total sum for the year. So for 105 he is 2.5 hours short.

所以这个人工作了大约10个小时。但是,如果你加上他的工作时间,它就会增加到7.5。我需要知道这种差异。如果他的总工作时间少于他的拳头,那那就是一个大问题。我需要知道一年的总和。所以对于105,他是2.5小时的短。

I don't know how to calculate the time worked because they are two separate rows. And then go through each row for each employee for each day. Can anyone help? Thank you so much!!

我不知道如何计算工作时间,因为它们是两个独立的行。然后每天为每位员工查看每一行。有人可以帮忙吗?非常感谢!!

employee    jobdate    sumHours DiffHours   ScheduledHours
105         20170123    7.5    -2.466666    10
105         20170124    1.5    -6.5         8

--------------------------------------------------------------------------------

More Test Data

更多测试数据

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

So this looks like he worked 3rd shift but he worked first. I think that will have to be added in. There is a shift column, thank goodness. Anyways he worked 4.25 hours which is correct, but it's saying he was scheduled for 20. minPunch and maxPunch are 226 and 1425 and they should be 346 and 2345.

所以这看起来他是第三班,但他先工作。我认为必须加入。有一个班次专栏,谢天谢地。无论如何,他工作4.25小时这是正确的,但它说他被安排在20分钟.minPunch和maxPunch是226和1425,他们应该是346和2345。

2 个解决方案

#1

You need to join two rows of data that match date and employee, but not punch; so you join the table against itself:

您需要加入两行与日期和员工匹配的数据,但不能打卡;所以你加入桌子对自己:

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 = 105

#2

You can convert the punch to minutes and the hours to minutes. From there you can check if hours < (maxpunch - minpunch)

您可以将打孔转换为分钟,将小时转换为分钟。从那里你可以检查是否小时<(maxpunch - minpunch)

This might get you going in the right direction:

这可能会让你朝着正确的方向前进:

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);

query:

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

returns:

+----------+----------+----------+-----------+----------------+
| Employee | jobdate  | SumHours | diffHours | ScheduledHours |
+----------+----------+----------+-----------+----------------+
|      105 | 20170123 | 7,50     | -2,50     | 10,00          |
+----------+----------+----------+-----------+----------------+

#1