基于MS Access与C#的考勤日志IN/OUT格式SQL查询求助

Hey there! Let's tackle this punch log transformation task for your MS Access + C# setup. Based on your sample data and desired output, I'll walk you through the SQL queries you need to get the job done.

First, let's align on the logic from your example:

• For each badge, we need to pair check-in (IN) and check-out (OUT) times.
• For badges with multiple consecutive punches (like ID 6 with 4 entries), it looks like we’re keeping the last punch of the first cluster as IN, and the last punch of the second cluster as OUT. Alternatively, if you just want the earliest valid check-in and latest valid check-out per badge (cleaning up duplicate punches in a short window), we can handle that too.

Option 1: Pair consecutive punches (1st ↔ 2nd, 3rd ↔ 4th, etc.) #

This query assigns a row number to each punch for a badge, then pairs odd-numbered rows (check-ins) with even-numbered rows (check-outs):

SELECT 
    ROW_NUMBER() OVER (ORDER BY t1.BADGE) AS ID,
    t1.BADGE AS Badge,
    t1.CHECKTIME AS [IN],
    t2.CHECKTIME AS [OUT]
FROM 
    (SELECT * FROM (
        SELECT 
            BADGE,
            CHECKTIME,
            (SELECT COUNT(*) FROM CheckLogs AS CL 
             WHERE CL.BADGE = CheckLogs.BADGE AND CL.CHECKTIME <= CheckLogs.CHECKTIME) AS RowNum
        FROM CheckLogs
    ) AS SubQuery WHERE RowNum MOD 2 = 1) AS t1
LEFT JOIN 
    (SELECT * FROM (
        SELECT 
            BADGE,
            CHECKTIME,
            (SELECT COUNT(*) FROM CheckLogs AS CL 
             WHERE CL.BADGE = CheckLogs.BADGE AND CL.CHECKTIME <= CheckLogs.CHECKTIME) AS RowNum
        FROM CheckLogs
    ) AS SubQuery WHERE RowNum MOD 2 = 0) AS t2
ON t1.BADGE = t2.BADGE AND t1.RowNum = t2.RowNum - 1
ORDER BY t1.BADGE;

Option 2: Clean up duplicate punches first, then get earliest IN / latest OUT #

If you want to filter out duplicate punches (like the 7:11 and 7:12 entries for badge 1708009) that happen within a 1-minute window, start with this deduplication query (save it as a separate query in Access):

SELECT 
    BADGE,
    CHECKTIME
FROM CheckLogs AS CL
WHERE NOT EXISTS (
    SELECT 1 FROM CheckLogs AS CL2
    WHERE CL2.BADGE = CL.BADGE 
    AND ABS(DateDiff("n", CL2.CHECKTIME, CL.CHECKTIME)) <= 1
    AND CL2.CHECKTIME > CL.CHECKTIME
)
ORDER BY BADGE, CHECKTIME;

Then use this cleaned-up data to generate your final formatted output:

SELECT 
    ROW_NUMBER() OVER (ORDER BY BADGE) AS ID,
    BADGE AS Badge,
    MIN(CHECKTIME) AS [IN],
    MAX(CHECKTIME) AS [OUT]
FROM DeduplicatedCheckLogs -- Replace with the name of your saved deduplication query
GROUP BY BADGE
ORDER BY BADGE;

Quick Notes: #

• Replace CheckLogs with your actual table name.
• ROW_NUMBER() works in Access 2010 and later. If you’re using an older version, you can generate the ID using a subquery with COUNT(*) similar to how we created row numbers.
• [IN] and [OUT] are wrapped in brackets because they’re reserved keywords in Access.

内容的提问来源于stack exchange，提问作者mr.jm