如何将记录选择为单个记录以确定时差

hgqdbh6s  于 2021-06-18  发布在  Mysql
关注(0)|答案(4)|浏览(275)

我有一个第三方时钟应用程序,我们用它来跟踪我们的员工的行动。它运行在mariadb上,但它相当混乱(imo)。前端是基于windows的,所有的处理都在那里进行。问题是我想提取一些数据进行实时报告。
所有计时活动都保存到一个表中。参见下面的示例。

INDX    _DATE_      _TIME_      SURNAME WIEGANDID       ZKINOUT
51      2018/09/03  5:52:04     Thakadu 000000000000AE  0
198     2018/09/03  14:04:29    Thakadu 000000000000AE  1
309     2018/09/03  21:54:06    Mabeo   000000000000BA  0
370     2018/09/04  5:47:20     Thakadu 000000000000AE  0
401     2018/09/04  6:00:09     Mabeo   000000000000BA  1
557     2018/09/04  14:04:57    Thakadu 000000000000AE  1
691     2018/09/04  21:53:33    Mabeo   000000000000BA  0
748     2018/09/05  5:47:20     Thakadu 000000000000AE  0
780     2018/09/05  6:00:34     Mabeo   000000000000BA  1
946     2018/09/05  14:05:32    Thakadu 000000000000AE  1
1089    2018/09/05  21:49:48    Mabeo   000000000000BA  0
1144    2018/09/06  5:50:41     Thakadu 000000000000AE  0
1174    2018/09/06  6:00:16     Mabeo   000000000000BA  1
1328    2018/09/06  14:09:28    Thakadu 000000000000AE  1
1482    2018/09/06  21:50:32    Mabeo   000000000000BA  0
1568    2018/09/07  5:58:48     Thakadu 000000000000AE  0
1555    2018/09/07  6:01:01     Mabeo   000000000000BA  1
1812    2018/09/07  14:05:47    Thakadu 000000000000AE  1
1845    2018/09/07  21:51:31    Mabeo   000000000000BA  0

挑战赛在马比奥从22:00:00一直工作到第二天早上06:00:00。而且,有时员工由于某种原因不打卡下班,系统会自动将他们注销,而不记录时间。
我想看到的结果是这样的。

DATE_IN     TIME_IN    DATE_OUT    TIME_OUT    SURNAME    WIEGANDID
2018/09/03  05:52:04   2018/09/03  14:04:29    Thakadu    000000000000AE    
2018/09/03  21:54:06   2018/09/04  06:00:09    Mabeo      000000000000BA
2018/09/04  05:47:20   2018/09/04  14:04:57    Thakadu    000000000000AE
2018/09/04  21:53:33   2018/09/05  06:00:16    Mabeo      000000000000BA

这样我就可以计算出每个雇员在一段时间内的实际上班时间。
我可以通过小组和个案的方式取得一些成功,但问题出在上夜班的员工身上。
任何帮助都将不胜感激。

gpfsuwkq

gpfsuwkq1#

正如arsuka大师所说,通过重新设计数据库,您当然可以更轻松地完成这项工作。不过,让我们看看能否用你所拥有的得到你的结果。我们还通过对临时表中的两个时间戳求差来计算工作小时数(浮动数),以防轮班已经结束。使用临时日期和时间列可以解决员工上夜班的问题。无论轮班是否在不同的一天结束,计算都是正确的。

SELECT COALESCE (a.DATE_IN, b.DATE_IN)                  AS DATE_IN,
       SUBSTR(COALESCE (a.TIME_IN, b.TIME_IN), 1, 8)    AS TIME_IN,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN COALESCE (a.DATE_OUT, 
            b.DATE_OUT) ELSE '' END                     AS DATE_OUT,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN 
            SUBSTR(COALESCE (a.TIME_OUT, b.TIME_OUT), 1, 8) ELSE '' END
                                                        AS TIME_OUT,
       COALESCE (a.SURNAME, b.SURNAME)                  AS SURNAME,
       COALESCE (a.WIEGANDID, b.WIEGANDID)              AS WIEGANDID,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN
            TIMESTAMPDIFF(SECOND, a.DATE_TIME_IN, b.DATE_TIME_OUT) / 3600 ELSE '' END
                                                        AS HOURS_WORKED
FROM (
    SELECT  _DATE_  AS  DATE_IN,
            _TIME_  AS  TIME_IN, 
            NULL    AS  DATE_OUT,
            NULL    AS  TIME_OUT,
            SURNAME,
            WIEGANDID,
            CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_IN
       FROM test 
      WHERE ZKINOUT = 0 )   AS   a,
    (
    SELECT  NULL    AS  DATE_IN,
            NULL    AS  TIME_IN, 
            _DATE_  AS  DATE_OUT,
            _TIME_  AS  TIME_OUT,
            SURNAME,
            WIEGANDID,
            CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_OUT
       FROM test
      WHERE ZKINOUT = 1 )   AS   b
WHERE a.WIEGANDID = b.WIEGANDID  
HAVING HOURS_WORKED < 15 AND HOURS_WORKED <> ''
ORDER BY 1, 2, 3, 4

这看起来有点复杂,它做了以下工作。创建两个临时表a和b。以下是()中的select语句。一个临时表包含登录记录,另一个包含注销记录。相应的“缺失”字段设置为空。
然后可以使用wiegandid将这些表相互连接起来。coalesce确保空值被过滤掉,并被包含正确值的其他临时表中的值替换。检查date\u time\u out是否大于date\u time\u in可确保date\u out和time\u out在相应的工作人员尚未注销时保持为空。
我在结果表中添加了一个新的列:hours\u worked。它包含作为浮点数的工作小时数。尚未结束的班次在结果集中被忽略(工作小时数)。为了消除重复和避免太多的复杂性,我们过滤掉了工作时间大于15小时的结果集记录,因为这在现实中不会发生,而且通常两个班次之间有足够的时间,我们不会赶上两个短班次。不是一个通用的解决方案,而是一个适用于此特定应用程序的解决方案。
您可以在create view语句中使用上述语句。然后可以使用视图而不是原始表,这将有助于降低复杂性。
下图显示了基于所有示例记录的上述查询结果。

j0pj023g

j0pj023g3#

好的,非常感谢所有的贡献者。我几乎得到了答案,只是还没有100%的答案。我使用了@rf1234建议的以下代码,谢谢你的回答。

SELECT COALESCE (a.DATE_IN, b.DATE_IN)                  AS DATE_IN,
       SUBSTR(COALESCE (a.TIME_IN, b.TIME_IN), 1, 8)    AS TIME_IN,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN COALESCE (a.DATE_OUT, 
            b.DATE_OUT) ELSE '' END                     AS DATE_OUT,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN 
            SUBSTR(COALESCE (a.TIME_OUT, b.TIME_OUT), 1, 8) ELSE '' END
                                                        AS TIME_OUT,
       COALESCE (a.SURNAME, b.SURNAME)                  AS SURNAME,
       COALESCE (a.WIEGANDID, b.WIEGANDID)              AS WIEGANDID,
       CASE WHEN ( b.DATE_TIME_OUT > a.DATE_TIME_IN ) THEN
            TIMESTAMPDIFF(SECOND, a.DATE_TIME_IN, b.DATE_TIME_OUT) / 3600 ELSE '' END
                                                        AS HOURS_WORKED
FROM (
    SELECT  _DATE_  AS  DATE_IN,
            _TIME_  AS  TIME_IN,
            NULL    AS  DATE_OUT,
            NULL    AS  TIME_OUT,
            SURNAME,
            WIEGANDID,
            CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_IN
          FROM _2018_09
         WHERE ZKINOUT = 0 AND SURNAME = 'MABEO'
         GROUP BY WIEGANDID, _DATE_ )   AS   a,
    (

    SELECT  NULL    AS  DATE_IN,
        NULL    AS  TIME_IN,
        _DATE_  AS  DATE_OUT,
        _TIME_  AS  TIME_OUT,
        SURNAME,
        WIEGANDID,
        CONCAT( _DATE_, ' ', _TIME_ ) AS DATE_TIME_OUT
      FROM _2018_09
     WHERE ZKINOUT = 1 AND SURNAME = 'MABEO'
     GROUP BY WIEGANDID, _DATE_)   AS   b
WHERE a.WIEGANDID = b.WIEGANDID
ORDER BY 1, 2, 3, 4

我已经修改了代码,现在只选择一个员工。我得到的结果几乎是我想要的,只是它似乎把表a的每条记录都连接到了表b的每条记录?下面是结果样本的图片。

z31licg0

z31licg04#

我上周刚刚完成了一个应用程序的创建,它可以监控我的员工的punchin和punchout时间,从而监控他们的出勤率,以及他们的休假、半天等,以进一步分析数据。
看到你提供的数据,我会建议什么使我的分析应用程序非常简单。使用punchin和punchout的时间戳数据类型,这一步不仅会合并你现有的列,而且会使你的工作没有障碍。
使用进出时间,你可以计算“工作时间”(简单的算术!)。你可以添加标志,看看是否有人在夜间工作(使用函数之间),等准备使用简单的函数将带头!
请让我知道如果你需要任何帮助。。。!!

相关问题