Sql query calculate worked hours

Question

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 27-Jul-22 23:21pm

Rodger007x

Updated 28-Jul-22 0:12am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2022-07-28T00:12:00

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               |