How to calculate Total working hours of employee by each session
You can do this with an outer apply.
I don't know what the OutType is used for, what the difference is with AccessType. You did not explain that or any other logic so I just assumed it should work on AccessType.
If that is not the case you can easy adapt the logic in the subquery below.
select e.EmpId,
e.LocationID,
e.logdate as SessionStart,
d.logdate as SessionEnd,
datediff(hour, e.logdate, d.logdate) as Hours
from emp e
outer apply ( select top 1 emp.logdate
from emp
where emp.empid = e.empid
and emp.accesstype = 2
and emp.logdate > e.logdate
order by emp.logdate
) d
where e.accesstype = 1
See the DBFiddle here
Result
| EmpId | LocationID | SessionStart | SessionEnd | Hours |
|---|---|---|---|---|
| 4545_1 | 4545 | 2022-05-25 16:27:41.217 | 2022-05-25 17:27:26.673 | 1 |
| 4545_1 | 4545 | 2022-05-25 17:31:30.333 | 2022-05-25 19:31:38.973 | 2 |
| 1212_8 | 1212 | 2022-05-26 10:21:38.973 | 2022-05-26 12:21:38.973 | 2 |
Get total working hours from SQL table
One more approach that I think is simple and efficient.
- It doesn't require modern functions like
LEAD - it works correctly if the same person goes in and out several times during the same day
- it works correctly if the person stays in over the midnight or even for several days in a row
- it works correctly if the period when person is "in" overlaps the start OR end date-time.
- it does assume that data is correct, i.e. each "in" is matched by "out", except possibly the last one.
Here is an illustration of a time-line. Note that start time happens when a person was "in" and end time also happens when a person was still "in":
All we need to do it calculate a plain sum of time differences between each event (both in and out) and start time, then do the same for end time. If event is in, the added duration should have a positive sign, if event is out, the added duration should have a negative sign. The final result is a difference between sum for end time and sum for start time.
summing for start:
|---| +
|----------| -
|-----------------| +
|--------------------------| -
|-------------------------------| +
--|====|--------|======|------|===|=====|---|==|---|===|====|----|=====|--- time
in out in out in start out in out in end out in out
summing for end:
|---| +
|-------| -
|----------| +
|--------------| -
|------------------------| +
|-------------------------------| -
|--------------------------------------| +
|-----------------------------------------------| -
|----------------------------------------------------| +
I would recommend to calculate durations in minutes and then divide result by 60 to get hours, but it really depends on your requirements. By the way, it is a bad idea to store dates as NVARCHAR.
DECLARE @StartDate datetime = '2015-08-01 00:00:00';
DECLARE @EndDate datetime = '2015-09-01 00:00:00';
DECLARE @EmpID nvarchar(6) = NULL;
WITH
CTE_Start
AS
(
SELECT
EmpID
,SUM(DATEDIFF(minute, (CAST(att.[date] AS datetime) + att.[Time]), @StartDate)
* CASE WHEN Funckey = 'EMPIN' THEN +1 ELSE -1 END) AS SumStart
FROM
PERS_Attendance AS att
WHERE
(EmpID = @EmpID OR @EmpID IS NULL)
AND att.[date] < @StartDate
GROUP BY EmpID
)
,CTE_End
AS
(
SELECT
EmpID
,SUM(DATEDIFF(minute, (CAST(att.[date] AS datetime) + att.[Time]), @StartDate)
* CASE WHEN Funckey = 'EMPIN' THEN +1 ELSE -1 END) AS SumEnd
FROM
PERS_Attendance AS att
WHERE
(EmpID = @EmpID OR @EmpID IS NULL)
AND att.[date] < @EndDate
GROUP BY EmpID
)
SELECT
CTE_End.EmpID
,(SumEnd - ISNULL(SumStart, 0)) / 60.0 AS SumHours
FROM
CTE_End
LEFT JOIN CTE_Start ON CTE_Start.EmpID = CTE_End.EmpID
OPTION(RECOMPILE);
There is LEFT JOIN between sums for end and start times, because there can be EmpID that has no records before the start time.
OPTION(RECOMPILE) is useful when you use Dynamic Search Conditions in T‑SQL. If @EmpID is NULL, you'll get results for all people, if it is not NULL, you'll get result just for one person.
If you need just one number (a grand total) for all people, then wrap the calculation in the last SELECT into SUM(). If you always want a grand total for all people, then remove @EmpID parameter altogether.
It would be a good idea to have an index on (EmpID,date).
SQL Server calculating working hours with multiple IN OUT
Assuming the IN and OUT are always in pair.
You can make use of LEAD() window function to get next check_time. And use CASE WHEN condition to determine it is IN or OUT time
select emp_id,
in_hrs = sum(in_mins) / 60.0,
check_date = convert(date, check_time),
out_hrs = sum(out_mins) / 60.0
from
(
select *,
in_mins = CASE WHEN check_type in ('C-IN', 'B-IN')
AND LEAD(check_type) OVER (PARTITION BY emp_id ORDER BY check_time) in ('C-OUT', 'B-OUT')
THEN DATEDIFF(MINUTE,
check_time,
LEAD(check_time) OVER (PARTITION BY emp_id ORDER BY check_time))
ELSE 0
END,
out_mins= CASE WHEN check_type in ('C-OUT', 'B-OUT')
AND LEAD(check_type) OVER (PARTITION BY emp_id ORDER BY check_time) in ('B-IN')
THEN DATEDIFF(MINUTE,
check_time,
LEAD(check_time) OVER (PARTITION BY emp_id ORDER BY check_time))
ELSE 0
END
from checkin_out_log
) d
group by emp_id, convert(date, check_time)
Edit : added condition to validate cases where IN without OUT or vice-versa. The in/out will be ignore and no calculation.
The added condition are
LEAD(check_type) OVER (PARTITION BY emp_id ORDER BY check_time) in ('C-OUT', 'B-OUT')
Postgres - calculate total working hours based on IN and OUT entry
The tricky part is to expand one row that covers two (calendar) days to two rows and allocating the hours of the "next" day correctly.
The first part is to get a pivot table that combines IN/OUT pairs into a single row.
A simple (yet not very efficient) approach is:
select ain.u_id,
ain.swipe_time as time_in,
(select min(aout.swipe_time)
from attendance aout
where aout.u_id = ain.u_id
and aout.status = 'OUT'
and aout.swipe_time > ain.swipe_time) as time_out
from attendance ain
where ain.status = 'IN'
The next step is to break up the rows with more than one day into two rows.
This assumes that you never have an IN/OUT pair that covers more than two days!
with inout as (
select ain.u_id,
ain.swipe_time as time_in,
(select min(aout.swipe_time)
from attendance aout
where aout.u_id = ain.u_id
and aout.status = 'OUT'
and aout.swipe_time > ain.swipe_time) as time_out
from attendance ain
where ain.status = 'IN'
), expanded as (
select u_id,
time_in::date as "date",
time_in,
time_out
from inout
where time_in::date = time_out::date
union all
select i.u_id,
x.time_in::date as date,
x.time_in,
x.time_out
from inout i
cross join lateral (
select i.u_id,
i.time_in,
i.time_in::date + 1 as time_out
union all
select i.u_id,
i.time_out::date,
i.time_out
) x
where i.time_out::date > i.time_in::date
)
select *
from expanded;
The above returns the following for your sample data:
u_id | date | time_in | time_out
-----+------------+---------------------+--------------------
1 | 2020-08-20 | 2020-08-20 16:00:00 | 2020-08-20 20:00:00
1 | 2020-08-20 | 2020-08-20 21:00:00 | 2020-08-21 00:00:00
1 | 2020-08-21 | 2020-08-21 00:00:00 | 2020-08-21 01:00:00
1 | 2020-08-21 | 2020-08-21 16:00:00 | 2020-08-21 19:00:00
How does this work?
So we first select all those rows that start and end on the same day with this part:
select u_id,
time_in::date as "date",
time_in,
time_out
from inout
where time_in::date = time_out::date
The second part of the union splits up the rows that span two days by using a cross join that generates one row with the original start time and midnight, and another from midnight to the original end time:
select i.u_id,
x.time_in::date as date,
x.time_in,
x.time_out
from inout i
cross join lateral (
-- this generates a row for the first of the two days
select i.u_id,
i.time_in,
i.time_in::date + 1 as time_out
union all
-- this generates the row for the next day
select i.u_id,
i.time_out::date,
i.time_out
) x
where i.time_out::date > i.time_in::date
At the end the new "expanded" rows are then aggregated by grouping them by user and date and left joined to the users table to get the username as well.
with inout as (
select ain.u_id,
ain.swipe_time as time_in,
(select min(aout.swipe_time)
from attendance aout
where aout.u_id = ain.u_id
and aout.status = 'OUT'
and aout.swipe_time > ain.swipe_time) as time_out
from attendance ain
where ain.status = 'IN'
), expanded as (
select u_id,
time_in::date as "date",
time_in,
time_out
from inout
where time_in::date = time_out::date
union all
select i.u_id,
x.time_in::date as date,
x.time_in,
x.time_out
from inout i
cross join lateral (
select i.u_id,
i.time_in,
i.time_in::date + 1 as time_out
union all
select i.u_id,
i.time_out::date,
i.time_out
) x
where i.time_out::date > i.time_in::date
)
select u.id,
u.u_name,
e."date",
min(e.time_in) as time_in,
max(e.time_out) as time_out,
sum(e.time_out - e.time_in) as duration
from users u
left join expanded e on u.id = e.u_id
group by u.id, u.u_name, e."date"
order by u.id, e."date";
Which then results in:
u_id | date | time_in | time_out | duration
-----+------------+---------------------+---------------------+----------------------------------------------
1 | 2020-08-20 | 2020-08-20 16:00:00 | 2020-08-21 00:00:00 | 0 years 0 mons 0 days 7 hours 0 mins 0.0 secs
1 | 2020-08-21 | 2020-08-21 00:00:00 | 2020-08-21 19:00:00 | 0 years 0 mons 0 days 4 hours 0 mins 0.0 secs
The "duration" column is an interval which you can format to your liking.
Online example
sql query to calculate total working hour from same Login/Logout column in MS Access 2010
I found the solution as follows, but the query is taking too much time, almost a minute. I think its because of many times of conversion. any idea how can i optimize this query. which function is faster for time conversion? the data type for logtime in table is date/time, should i change it to any other type like text?
SELECT EmployeeID,
month(LogDate),
SUM(int(DateDiff("s", '00:00:00',Duration))) AS abc
FROM (SELECT AccessLog.EmployeeID,
AccessLog.LogDate,
AccessLog.TerminalID,
AccessLog.LogTime,
Format((SELECT max(LogTime)
FROM AccessLog AS Alias
WHERE Alias.LogTime < AccessLog.LogTime
AND Alias.EmployeeID = AccessLog.EmployeeID
AND Alias.LogDate = AccessLog.LogDate
AND (Alias.TerminalID)<>"iGuard1A"
And (Alias.TerminalID)<>"iGuard1B"
AND Alias.EmployeeID = AccessLog.EmployeeID),
"hh:nn:ss") AS PrevTime,
Format((ElapsedTime(iif(PrevTime = '',logtime,prevtime),[LogTime])),"hh:nn:ss") AS Duration,
AccessLog.InOut
FROM AccessLog
WHERE (((AccessLog.TerminalID)<>"iGuard1A"
And (AccessLog.TerminalID)<>"iGuard1B")
AND ((AccessLog.EmployeeID) Like "2*")
AND ((AccessLog.InOut)="OUT"))
ORDER BY AccessLog.EmployeeID, AccessLog.LogDate, AccessLog.LogTime)
GROUP BY EmployeeID, month(LogDate);
Get total hours worked in a day mysql
SELECT `User_id`, time(sum(`Date_time`*(1-2*`in_out`)))
FROM `whatever_table` GROUP BY `User_id`;
The (1-2*`in_out`) term gives every login event a -1 factor and every logout event a +1 factor. The sum function takes the sum of the Date_time column, and GROUP BY `User_id` makes that the sum for each different user is created.
Related Topics
How to Select the Last Record of a Table in SQL
Select All Dates Between First Day of Month and Current Date
Select Ids from Multiple Rows Where Column Values Satisfy One Condition But Not Another
How to Create Column in SQL Query With Custom Text
Phone Number Display Method, SQL Query
How to Convert from Blob to Text in MySQL
How to Return a Flag If Exist Id in Another Table MySQL
How to Count Number of Digits After a Decimal Place
Mysql: Alter Table If Column Not Exists
Select Different Values from Same Column in a Table and Display It Under Different Columns
How to Add Leading Zero When Number Is Less Than 10
Row Numbers in Query Result Using Microsoft Access
How to Connect MySQL Workbench to Running MySQL Inside Docker
Sql - Select Parent and Child Records in an Order
State Wise Data of Country and City
Jsonb Array Contains Like or and and Operators
Way to Find Data of a SQL Table With Same Status for Consecutive 3 Days