Sql query to create a calculated field
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'
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]
If you need to restrict the DateRange you add the needed conditions to the row where t1.Addr='m001-1-In'
eviB
Updated on July 31, 2022Comments
-
eviB almost 2 years
I have a database table like this:
I hope I can explain this well, so you can understand.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.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
The
DateTime
Column is of typeString
, notDateTime
. I am working with MS Access Database.