Sql查询创建一个计算字段

我希望我能够很好地解释这一点,所以你可以理解.

我想计算每个员工工作了多少小时.
例如,对于“Arjeta Domi”,我们有Cell(2,3) – Cell(3,3)Cell(4,3)Cell(5,3),使每个logOut时间与登录时间不同.

我想要的最终表将包含以下列：CardNo,UserName,Date,PauseTime,WorkTime

我试过这个查询：取自duplicate

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数据库.

解决方法

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’的行中

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!