我有一个像这样的数据库表：
我希望我能够很好地解释这一点,所以你可以理解.
我想计算每个员工工作了多少小时.
例如,对于“Arjeta Domi”,我们有Cell(2,3) – Cell(3,3)Cell(4,3)Cell(5,3),使每个logout时间与登录时间不同.
我想要的最终表将包含以下列：CardNo,UserName,Date,PauseTime,WorkTime
SELECT DISTINCT
[Card NO],[User Name],(
SELECT
MIN(DateTime) AS [Enter Time],MAX(DateTime) AS [Exit Time],MAX(DateTime) - MIN(DateTime) AS [Inside Hours]
FROM
ExcelData
)
FROM
ExcelData
GROUP BY
[Card NO],DateTime
DateTime列的类型为String,而不是DateTime.
我正在使用MS Access数据库.
解决方法
选择带有’m001-1-In’的所有行,将DateTime作为I,并将拟合’m001-1-Exit’行添加到此处,子查询为O,这将如下所示：
SELECT t1.[Card No],t1.[User Name],dateTime as I,(Select TOP 1 dateTime from Tab t2 where t2.[Card No]= t1.[Card No] and t2.[User Name]= t1.[User Name] and t2.Addr='m001-1-Exit' and t2.DateTime>t1.datetime ORDER by DateTime) as O FROM Tab t1 where t1.Addr='m001-1-In'
现在很容易将其封装起来,在下面显示为Prepared并将SUM和Grouping添加到：
SELECT [Prepared].[Card No],[Prepared].[User Name],SUM(DateDiff('n',I,O))/60 AS Hours
FROM (
SELECT t1.[Card No],(Select TOP 1 dateTime from Tab t2 where t2.[Card No]= t1.[Card No]
and t2.[User Name]= t1.[User Name] and t2.Addr='m001-1-Exit'
and t2.DateTime>t1.datetime ORDER by DateTime) as O
FROM Tab t1
where t1.Addr='m001-1-In'
) AS [Prepared]
GROUP BY [Prepared].[Card No],[Prepared].[User Name]
如果您需要限制DateRange,则将所需条件添加到t1.Addr =’m001-1-In’的行中
