sql - 如何将日期/时间安排到上午输入/输出和下午输入/输出

Question

我正在做一个工资单系统，我从 zktechnology 购买了 B3 tft，并想安排记录。

目前，我可以使用以下格式从生物特征中提取数据:

计算 EmpID InOutMode 日期
1 1 0 2012 年 8 月 20 日 07:49:01
2 1 1 2012 年 8 月 20 日 12:08:21
3 1 0 2012 年 8 月 20 日 12:43:10
4 1 1 2012 年 8 月 20 日 17:56:15
5 2 0 2012 年 8 月 20 日 07:53:11
6 2 1 2012 年 8 月 20 日 12:02:01
7 2 0 2012 年 8 月 20 日 12:39:56
8 2 1 2012 年 8 月 20 日 17:20:43
9 1 0 2012 年 8 月 21 日 08:10:20
10 1 1 2012 年 8 月 21 日 12:01:26
11 1 0 2012 年 8 月 21 日 13:03:11
12 1 1 2012 年 8 月 21 日 17:11:15
13 2 0 2012 年 8 月 21 日 07:48:26
14 2 1 2012 年 8 月 21 日 12:14:58
15 2 0 2012 年 8 月 21 日 12:59:31
16 2 1 2012 年 8 月 21 日 17:20:12

输入输出模式:

0 = 输入，1 = 输出

现在，我想像这样转换上面的数据:

EmpID 日期 AM_In AM_Out PM_In PM_Out
1 2012 年 8 月 20 日 07:49:01 12:08:21 12:43:10 17:56:15
2 2012 年 8 月 20 日 07:53:11 12:02:01 12:39:56 17:20:43
1 2012 年 8 月 21 日 08:10:20 12:01:26 13:03:11 17:11:15
2 2012 年 8 月 21 日 07:48:26 12:14:58 12:59:31 17:20:12

所以我可以用 EmpID、Date、AM_In、AM_Out、PM_In、PM_Out 字段将它保存到数据库中。

我以前在这里看到过类似的代码，但我不记得网址了。

更新:

VB.net 代码或 ms Access 格式的 sql 是可以接受的。

Answer 1

创建两个交叉表和一个查询以将它们连接到可用的日期和员工可能是最简单的。

凌晨 1 点交叉表

TRANSFORM Min(tm.Date) AS MinOfDate
SELECT Format([Date],"dd/mm/yyyy") AS dt, tm.EmpID
FROM tm
GROUP BY Format([Date],"dd/mm/yyyy"), tm.EmpID
PIVOT tm.InOutMode;

下午 2 点交叉表

TRANSFORM Max(tm.Date) AS MinOfDate
SELECT Format([Date],"dd/mm/yyyy") AS dt, tm.EmpID
FROM tm
GROUP BY Format([Date],"dd/mm/yyyy"), tm.EmpID
PIVOT tm.InOutMode;

其中 tm 是您的表的名称。

然后你可以加入这些。

SELECT Alldates.dt, 
       Alldates.empid, 
       am.[0] AS [Am In], 
       am.[1] AS [Am Out], 
       pm.[0] AS [Pm In], 
       pm.[1] AS [Pm Out] 
FROM   ((SELECT DISTINCT Format([date], "dd/mm/yyyy") AS dt, 
                        empid 
        FROM   tm) AS Alldates 
        LEFT JOIN am 
               ON ( Alldates.empid = am.empid ) 
                  AND ( Alldates.dt = am.dt )) 
       LEFT JOIN pm 
              ON ( Alldates.empid = pm.empid ) 
                 AND ( Alldates.dt = pm.dt );