时间戳

首页 > 解决方案 > MS Access\Jet SQL - 员工上班和下班时间、同一张表、每位员工的全职工作总和

ms-access - MS Access\Jet SQL - 员工上班和下班时间、同一张表、每位员工的全职工作总和

问题描述

我有下表名为“时间”。它保存着工作人员的进出（morn_in、morn_out、下午进、下午出）。

时间表 我想要实现的是按staff_ref分组的输出，他们工作时间的总和，就像这样；

staff_ref|   total_worked|   
   U1            18:00

我目前使用 4 个单独的查询来选择每个“类型”（morn_in、morn_out 等），type='morn_in' 的查询如下，这个查询称为 [101 am in]（稍后会引用它）；

SELECT 
staff_ref
, time
, Format([date],"dd/mm/yyyy") AS t_date
FROM timings
WHERE (((type)='morn_in'));

然后我在早上时间之间对 DateDiff 求和；

SELECT 
[101 am in].staff_ref
, Sum(DateDiff("n",[101 am in].[time],[102 am out].[time])) AS morning_mins

FROM [101 am in] INNER JOIN [102 am out] 
 ON ([101 am in].staff_ref = [102 am out].staff_ref) 
 AND ([101 am in].date = [102 am out].date)
GROUP BY [101 am in].staff_ref;

然后在另一个查询中分别对下午时间（与上面相同）的 datediff 求和，然后将这两个查询的结果加在一起得到我的总数。

如您所见，它非常复杂，而且解释起来并不简单。

我想知道如何组合查询，所以如果可能的话，我只需要一个语句来返回结果。

如果我解释得很糟糕，请告诉我，我会做出修改。
谢谢

编辑 1 - 03/07/2018
我使用下面的代码来提取我需要的数据，因为它是“时间”数据类型，我必须计算总分钟数，然后对小时执行 \60，对分钟执行 MOD 60结果超过24小时。 

SELECT 
staff_ref, 
CLng(24*60*CDate(Sum(IIf([type]='morn_out',[time],0)-IIf([type]='morn_in',[time],0)+
IIf([type]='afternoon_out',[time],0)-IIf([type]='afternoon_in',[time],0)))) AS time_mins,
[time_mins]\60 & Format([time_mins] Mod 60,"\:00") AS convert_backHHMM

FROM timings

GROUP BY 
staff_ref, 
fix(time)
;

我想知道我将如何处理表格有“morning_out”时间但没有“morning_in”时间（反之亦然）的可能性。[系统的前端确实可以防止这种情况，但我真的很好奇并想学习}。

感谢您的帮助

标签： ms-accessms-access-2016jet-sql

解决方案

试试这个：

SELECT 
    staff_ref,
    Sum(IIf([Type] = "morn_out", [date], 0) - IIf([Type] = "morn_in", [date], 0) +
        IIf([Type] = "afternoon_out", [date], 0) - IIf([Type] = "afternoon_in", [date], 0)) As TotalTime
FROM 
    [101 am in]
GROUP BY
    staff_ref,
    Fix([date]);

将格式应用于总时间，例如h:nn

推荐阅读