Click here to Skip to main content
15,443,549 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table that collects clocking transactions
tblTR(PK-int tagcode,Date trDate,Time trTime,In_Out)
INSERT #tblTR
VALUES
(1, '2019-01-01', '09:00:00', 'In'),
(1, '2019-01-01', '12:00:00', 'Out'),
(1, '2019-01-01', '13:00:00', 'In'),
(1, '2019-01-01', '17:00:00', 'Out'),
(2, '2019-01-02', '23:00:00', 'In'),
(2, '2019-01-01', '12:00:00', 'Out'),
(2, '2019-01-01', '13:00:00', 'In'),
(2, '2019-01-01', '17:00:00', 'Out'),
(2, '2019-01-02', '23:00:00', 'In'),
(2, '2019-01-03', '07:00:00', 'Out'),
(1, '2019-01-03', '07:00:00', 'Out');

How do i calculate the hours worked with a procedure or function in MSSQL?
I know i need the start and end dates then convert the time into 24hour, so i can transform or change the minutes into decimals, deduct the lunch hour and calculate the hours worked. A user can clock out and in at any given time.

What I have tried:

Create
FUNCTION [dbo].[fn_GetTotalWorkingDays]
(@DateFrom DATE,
 @DateTo   DATE
)
RETURNS INT
AS
     BEGIN
         DECLARE @TotWorkingDays INT= 0;
         WHILE @DateFrom <= @DateTo
             BEGIN
                 IF DATENAME(WEEKDAY, @DateFrom) IN('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday','Saturday','Sunday')
                     BEGIN
                         SET @TotWorkingDays = @TotWorkingDays + 1;
                 END;
                 SET @DateFrom = DATEADD(DAY, 1, @DateFrom);
             END;
         RETURN @TotWorkingDays;
     END;

and i am using
[dbo].[fn_GetTotalWorkingHours]
(
    @DateFrom Datetime,
    @DateTo Datetime
)
RETURNS DECIMAL(18,2)
AS
BEGIN
 
DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2)
 
SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
				    -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
					   
SET @TotalTimeDiff =
(
    SELECT DATEDIFF(SECOND,
                   (
                       SELECT CONVERT(TIME, @DateFrom)
                   ),
                   (
                       SELECT CONVERT(TIME, @DateTo)
                   )) / 3600.0
);
 
RETURN  (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff)
 
END


but i am having trouble putting it all together...
Posted
Updated 28-Jul-22 0:12am

1 solution

For a start, you have some spurious data in your sample. Eg: tagcode 1 clocks out on 1st January at 17:00, and then clocks out again on 3rd January at 07:00, without a corresponding clock-in event.

Similarly, tagcode 2 clocks in twice on 2nd January at 23:00.

At a guess, one of the two 23:00 clock-in events should have been for tagcode 1.

If we make that change, then the following query:
SQL
WITH cteSource As
(
    SELECT
        tagcode,
        DateAdd(second, ((DatePart(hour, trTime) * 60) + DatePart(minute, trTime)) * 60 + DatePart(second, trTime), CAST(trDate As datetime2(0))) As LogTime,
        CASE In_Out WHEN 'In' THEN CAST(0 As bit) WHEN 'Out' THEN CAST(1 As bit) END As IsOut
    FROM
        #tblTR
)
SELECT
    I.tagcode,
    I.LogTime As InTime,
    O.LogTime As OutTime
FROM
    cteSource As I
    CROSS APPLY
    (
        SELECT TOP 1 S.LogTime
        FROM cteSource As S
        WHERE S.tagcode = I.tagcode
        And S.LogTime >= I.LogTime
        And S.IsOut = 1
        ORDER BY S.LogTime
    ) As O
WHERE
    I.IsOut = 0
;
produces the following output:
| tagcode | InTime              | OutTime             |
|=========|=====================|=====================|
| 1       | 2019-01-01 09:00:00 | 2019-01-01 12:00:00 |
| 1       | 2019-01-01 13:00:00 | 2019-01-01 17:00:00 |
| 1       | 2019-01-02 23:00:00 | 2019-01-03 07:00:00 |
| 2       | 2019-01-01 13:00:00 | 2019-01-01 17:00:00 |
| 2       | 2019-01-02 23:00:00 | 2019-01-03 07:00:00 |
From there, it's just a case of using DateDiff to calculate the difference between the in and out times in whatever unit you need. For example:
SQL
WITH cteSource As
(
    SELECT
        tagcode,
        DateAdd(second, ((DatePart(hour, trTime) * 60) + DatePart(minute, trTime)) * 60 + DatePart(second, trTime), CAST(trDate As datetime2(0))) As LogTime,
        CASE In_Out WHEN 'In' THEN CAST(0 As bit) WHEN 'Out' THEN CAST(1 As bit) END As IsOut
    FROM
        #tblTR
),
cteHours As
(
    SELECT
        I.tagcode,
        DateDiff(hour, I.LogTime, O.LogTime) As WorkedHours
    FROM
        cteSource As I
        CROSS APPLY
        (
            SELECT TOP 1 S.LogTime
            FROM cteSource As S
            WHERE S.tagcode = I.tagcode
            And S.LogTime >= I.LogTime
            And S.IsOut = 1
            ORDER BY S.LogTime
        ) As O
    WHERE
        I.IsOut = 0
)
SELECT
    tagcode,
    SUM(WorkedHours) As TotalWorkedHours
FROM
    cteHours
GROUP BY
    tagcode
;
| tagcode | TotalWorkedHours |
|=========|==================|
| 1       | 15               |
| 2       | 12               |
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900