如何根据原始时间表数据计算每日和每月总小时数

Question

>我在 Access 中有如下表，我想每天和每月计算员工的总小时数

EmpID InTime    OutTime     Date
1     09:00 AM  10:00 AM    1-1-14
2     09:30 AM  09:30 AM    1-1-14
3     09:00 AM  10:00 AM    1-1-14
4     12:00 AM  05:00 AM    1-1-14
1     11:00 AM  10:00 PM    1-1-14
2     12:30 PM  09:30 PM    1-1-14
3     01:00 PM  10:00 PM    1-1-14
4     02:00 PM  05:00 PM    1-1-14

我正在使用 DateDiff 函数，但仍然要获得我需要的最终输出，我想我将不得不做很多编码/嵌套查询。我说的对吗？

Answer 1

用于名为 [TimeDataRaw] 的表中的原始数据

EmpID  InTime    OutTime   Date    
-----  --------  --------  --------
    1  09:00 AM  10:00 AM  1/1/2014
    2  09:30 AM  09:30 AM  1/1/2014
    3  09:00 AM  10:00 AM  1/1/2014
    4  12:00 AM  05:00 AM  1/1/2014
    1  11:00 AM  10:00 PM  1/1/2014
    2  12:30 PM  09:30 PM  1/1/2014
    3  01:00 PM  10:00 PM  1/1/2014
    4  02:00 PM  05:00 PM  1/1/2014

您可以在 Access 中创建一个名为 [TimeDataView] 的已保存查询（也称为"视图"），如下所示

SELECT 
    EmpID,
    [Date],
    Year([Date]) AS DateYear,
    Month([Date]) AS DateMonth,
    Day([Date]) AS DateDay,
    InTime,
    OutTime,
    DateDiff("h", CDate([Date] & " " & InTime), CDate([Date] & " " & OutTime)) AS FullHours
FROM TimeDataRaw

返回

EmpID  Date      DateYear  DateMonth  DateDay  InTime    OutTime   FullHours
-----  --------  --------  ---------  -------  --------  --------  ---------
    1  1/1/2014      2014          1        1  09:00 AM  10:00 AM          1
    1  1/1/2014      2014          1        1  11:00 AM  10:00 PM         11
    2  1/1/2014      2014          1        1  09:30 AM  09:30 AM          0
    2  1/1/2014      2014          1        1  12:30 PM  09:30 PM          9
    3  1/1/2014      2014          1        1  01:00 PM  10:00 PM          9
    3  1/1/2014      2014          1        1  09:00 AM  10:00 AM          1
    4  1/1/2014      2014          1        1  02:00 PM  05:00 PM          3
    4  1/1/2014      2014          1        1  12:00 AM  05:00 AM          5

然后，您可以针对该视图运行查询，以 SUM（FullHours） 和 GROUP BY DateMonth 或 DateYear 或您想要聚合值的任何方式，例如：

SELECT EmpID, DateYear, DateMonth, SUM(FullHours) AS TotalHours
FROM TimeDataView
GROUP BY EmpID, DateYear, DateMonth

返回

EmpID  DateYear  DateMonth  TotalHours
-----  --------  ---------  ----------
    1      2014          1          12
    2      2014          1           9
    3      2014          1          10
    4      2014          1           8

（请注意，DateDiff("h", ...) 将仅返回 time1 和 time2 之间的完整小时数，因此"09：00 AM"和"09：59 AM"之间的"小时差异"将为零。