MS Access TRANSFORM SQL工时计算及多表关联VB.NET兼容查询需求

API 探路者

2026-6-19

VB.NET 兼容的MS Access TRANSFORM SQL 实现方案

以下是满足需求的SQL语句，可直接在VB.NET中调用，无需依赖Access专属内置函数，同时解决多员工数据匹配问题：

TRANSFORM 
    FIRST(IIF(ABSEN.STATUS = 'NOT PRESENT', NULL, ABSEN.IN)) AS [IN],
    FIRST(IIF(ABSEN.STATUS = 'NOT PRESENT', NULL, ABSEN.OUT)) AS [OUT],
    FIRST(IIF(ABSEN.STATUS = 'NOT PRESENT', NULL, MASTERDAYS.DEFREST)) AS [DEFREST],
    FIRST(IIF(ABSEN.STATUS = 'NOT PRESENT', NULL, ABSEN.OUT - ABSEN.IN - MASTERDAYS.DEFREST)) AS [DUROFWORK],
    FIRST(IIF(ABSEN.STATUS = 'NOT PRESENT' OR ABSEN.IN <= MASTERDAYS.DEFIN, NULL, ABSEN.IN - MASTERDAYS.DEFIN)) AS [LATEIN]
SELECT 
    MASTERID.EMPID,
    MASTERID.NAME,
    ABSEN.DATE
FROM 
    (MASTERID 
    LEFT JOIN ABSEN ON MASTERID.EMPID = ABSEN.EMPID)
    LEFT JOIN MASTERDAYS ON ABSEN.DATE = MASTERDAYS.DATE AND MASTERID.EMPID = MASTERDAYS.EMPID
GROUP BY 
    MASTERID.EMPID,
    MASTERID.NAME,
    ABSEN.DATE
PIVOT 
    ABSEN.STATUS;

关键逻辑说明

关联匹配：通过MASTERID.EMPID = ABSEN.EMPID和ABSEN.DATE = MASTERDAYS.DATE AND MASTERID.EMPID = MASTERDAYS.EMPID的双重关联条件，确保每个员工的每日考勤记录与工作日基准唯一匹配，避免多员工数据交叉错误。
空值控制：使用IIF函数判断STATUS为NOT PRESENT时，将DEFREST、IN、OUT、DUROFWORK、LATEIN字段设为NULL。
迟到计算：仅当IN时间晚于DEFIN且状态不为缺勤时，才计算LATEIN差值，否则返回空值。
工作时长计算：仅在非缺勤状态下，通过OUT - IN - DEFREST计算实际工作时长，缺勤时返回空值。