Display work minutes between IN and OUT Punches of employees

SQL command to display the working minutes between IN and OUT punces of employees.

Case study. One of our customer wants to know the time spent in the cafeteria by an employee. We installed 2 devices one for IN while he enter the canteen and another for OUT when he comes out of canteen. There is an anti-pass back functionality enabled in the device to ensure IN and OUT punches are marked. These data punces are stored in a SQL database. Now customer want to see the time duration spent in the canteen, for that below SQL query is written to find out the totoal time spent in the canteen. Such kind of time theft or loss can be find out by using the Biometric attendnace devices from AmpleTrails. For more information call us on 9315441078/53.

--SELECT TOP (10) [DeviceLogId] ,[DeviceId] ,[UserId],[LogDate] ,[Direction]    
--  FROM [worldwide_eTime].[dbo].[DeviceLogs_7_2024]
--  where UserId ='2' and DeviceId ='31'
--  order by LogDate asc


WITH CTE AS (
    SELECT
        [DeviceLogId],    
        [DeviceId],
        [UserId],
        [LogDate],
        [Direction],
        LEAD([LogDate]) OVER (PARTITION BY [UserId] ORDER BY [LogDate]) AS NextLogDate,
        LEAD([DeviceLogId]) OVER (PARTITION BY [UserId] ORDER BY [LogDate]) AS NextDeviceLogId,
        LEAD([Direction]) OVER (PARTITION BY [UserId] ORDER BY [LogDate]) AS NextDirection
    FROM [worldwide_eTime].[dbo].[DeviceLogs_7_2024]
   -- WHERE [UserId] ='2' AND [DeviceId] ='31'
)
SELECT
   -- TOP (10)
    [DeviceId],
    [UserId],
    CAST([LogDate] AS DATE) AS AttendanceDate,
    CASE
        WHEN [Direction] = 'in' THEN [LogDate]
    END AS LogDate_In,

CASE
        WHEN [Direction] = 'in' AND NextDirection = 'out' THEN NextLogDate
    END AS LogDate_Out,
CASE
        WHEN [Direction] = 'in' AND NextDirection = 'out' THEN DATEDIFF(MINUTE, [LogDate], NextLogDate)
    END AS TimeDifference,
    CASE
        WHEN [Direction] = 'in' THEN [DeviceLogId]
    END AS DeviceLogId_In,
   CASE
        WHEN [Direction] = 'in' AND NextDirection = 'out' THEN NextDeviceLogId
    END AS DeviceLogId_Out
   

FROM CTE
WHERE [Direction] = 'in'
ORDER BY [LogDate] ASC

If you have requirement for such custom SQL queries please share your requirements on in**@*********ls.com and one of our experts will contact you with the quotation.

Name *

Email *

Mobile Number *

I agree to receive all updates via SMS, WhatsApp, RCS, Email, and any other communication channel.

• I agree

My Requirement *

Submit