Sql查询创建一个计算字段 [英] Sql query to create a calculated field

问题描述

我有一个这样的数据库表：



我希望我能解释一下，以便你能理解。

I have a database table like this:

I hope I can explain this well, so you can understand.

我想计算每个员工工作的小时数。

例如对于Arjeta Domi，我们有Cell（2,3） - Cell（3,3）+ Cell（4,3）+ Cell（5,3），使得每个logOut时间与登录时间之间的差异。

I want to calculate how many hours each employee has worked.
For example for "Arjeta Domi" we have Cell(2,3) - Cell(3,3) + Cell(4,3) + Cell(5,3), making the difference of each logOut time with Login time.

>

我想要的最终表格将包含以下列： CardNo ， UserName ，日期， PauseTime ， WorkTime

The final table that I want will have these columns: CardNo, UserName, Date, PauseTime, WorkTime

我试过这个查询：取自重复

I tried this query: taken from the 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], [User Name], DateTime

DateTime 列的类型为 String ，而不是 DateTime 。
我正在使用MS Access数据库。

The DateTime Column is of type String, not DateTime. I am working with MS Access Database.

推荐答案

选择所有具有m001-1-In作为I，并将适配的'm001-1-Exit'行添加到这个子查询作为O，这将是这样：

Select all rows with 'm001-1-In' with DateTime as I and add the fitting 'm001-1-Exit' rows to this with a Subquery as O, this will look like this:

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' 

现在很容易封装这个，show作为下面的准备，并将我们的SUM和分组添加到此：

Now it's easy to encapsulate this, show as Prepared below and add our SUM and Grouping to this:

SELECT [Prepared].[Card No], [Prepared].[User Name], SUM(DateDiff('n',I,O))/60 AS Hours
FROM (
      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' 
)  AS [Prepared]
GROUP BY [Prepared].[Card No], [Prepared].[User Name]

如果你需要限制DateRange，你需要添加条件到其中t1.Addr ='m001-1-In'

If you need to restrict the DateRange you add the needed conditions to the row where t1.Addr='m001-1-In'

这篇关于Sql查询创建一个计算字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！