Calculating checkin and checkout for employee attendance records.

Question

0.00/5 (No votes)

See more:

Greetings family. Please am not get the desire result am looking for in my employee attendance table. I want to calculate the total hours each employee worked for the day base on check in and check out input. Below is what I have tried. Hope to get help on this. Thanks in advance.

What I have tried:

SQL

Create Table EmployeeAttendanceDetails
(
     EmployeeID    VarChar(10) Not Null,
     EmployeeName  VarChar(50) ,
     Designation   VarChar(50) ,
     WorkingDate   Date        Not Null,
     InputType     VarChar(10) Not Null,
     TimeInput     Time        Not Null
)

--Sample Data For Testing

EmployeeID EmployeeName            Department         WorkingDate InputType TimeI
EMP105     Success Abena Gyasiwaa  Marketing Manager  2017-01-12  Time In   08:07
EMP101     Samuel Osei Banahene    Deputy MD          2017-01-12  Time In   08:08
EMP104     George Mensah           Supervisor         2017-01-12  Time In   08:10
EMP103     Nancy Koffour           Department Head    2017-01-12  Time In   08:10
EMP102     Andrews Appiah Sarkodie Managing Director  2017-01-12  Time In   08:12
EMP101     Samuel Osei Banahene    Deputy MD          2017-01-12  Time Out  17:35
EMP102     Andrews Appiah Sarkodie Managing Director  2017-01-12  Time Out  17:35
EMP103     Nancy Koffour           Department Head    2017-01-12  Time Out  17:37
EMP104     George Mensah           Supervisor         2017-01-12  Time Out  17:38
EMP105     Success Abena Gyasiwaa  Marketing Manager  2017-01-12  Time Out  17:40

--At the the close of work I want to select the records and calculate the hours for the --day and insert the records into a new table. Below is the table and the select ----statement.

SQL

Create Table EmployeeAttendanceTable
(
	EmployeeID		VarChar(10),
	EmployeeName		VarChar(50),
	WorkingDate		Date,
	CheckIn			VarChar(10),
	CheckOut		VarChar(10),
	[Hours]			DECIMAL(18,2) 
)

--Select statement
Insert Into EmployeeAttendanceTable

Select I.EmployeeID,I.EmployeeName,Cast(WorkingDate As Date)As [Date],
	Convert(VarChar(10),I.TimeInput,108)As CheckIn,
	Convert(VarChar(10),O.TimeInput,108)As CheckOut,
	CAST(DATEDIFF(MINUTE,I.TimeInput,O.TimeInput)/60.0 AS DECIMAL(18,2)) [Hours]
From EmployeeAttendanceDetails I
OUTER APPLY
(Select TOP 1
	InputType,
	TimeInput
	From EmployeeAttendanceDetails t
        Where t.BranchCode = 'B101'
	And t.WorkingDate ='12 JAN 2017'
	And t.TimeInput > I.TimeInput
	Order By t.TimeInput
)O
Where I.InputType ='Time In'
And  O.InputType ='Time Out'
--My problem is that is able to calculate only the last entry for the checkin employee only.
--Result
EmployeeID EmployeeName             WorkingDate CheckIn  CheckOut   Hours
EMP102	   Andrews Appiah Sarkodie  2017-01-12	08:12:11  17:35:11   9.38

Posted 2-May-20 19:55pm

NanaKwame

Updated 7-May-20 8:08am

MadMyche

v2

Add a Solution

Comments

NanaKwame 4-May-20 16:54pm

@Richard MacCutchan please it did not resolve it. Thanks anyway.

2 solutions

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 MacCutchan · Accepted Answer · 2020-05-02T22:48:00

WorkingDate Date,
CheckIn VarChar(10),
CheckOut VarChar(10),
[Hours] DECIMAL(18,2)

Why are you using varchar types for times? Use DateTime type for checkin and checkout, which allows you to easily calculate the total time at work. It also allows for working periods that continue overnight. And since the times include the date you do not need the WorkinDate or the Hours fields.

Richard Deeming · Accepted Answer · 2020-05-07T08:09:00

Something like this should work:

SQL

WITH cteDates As
(
    SELECT
        EmployeeID,
        EmployeeName,
        Designation,
        CASE InputType
            WHEN 'Time In' THEN 0
            ELSE 1
        END As IsTimeOut,
        WorkingDate,
        TimeInput,
        DateAdd(day, DateDiff(day, '19000101', WorkingDate), Cast(TimeInput As datetime2(0))) As LogTime,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY WorkingDate, TimeInput) As RN
    FROM
        @AD
)
SELECT
    I.EmployeeID,
    I.EmployeeName,
    I.Designation,
    I.WorkingDate,
    I.TimeInput As CheckIn,
    O.TimeInput As CheckOut,
    Cast(DateDiff(minute, I.LogTime, O.LogTime) / 60. As decimal(18, 2)) As [Hours]
FROM
    cteDates As I
    LEFT JOIN cteDates As O
    ON O.EmployeeID = I.EmployeeID
    And O.RN = I.RN + 1
WHERE
    I.IsTimeOut = 0
;

Output:

plain

EmployeeID EmployeeName            Designation       WorkingDate CheckIn   CheckOut  Hours
EMP101     Samuel Osei Banahene    Deputy MD         2017-01-12  08:08:00  17:35:00  9.45
EMP102     Andrews Appiah Sarkodie Managing Director 2017-01-12  08:12:00  17:35:00  9.38
EMP103     Nancy Koffour           Department Head   2017-01-12  08:10:00  17:37:00  9.45
EMP104     George Mensah           Supervisor        2017-01-12  08:10:00  17:38:00  9.47
EMP105     Success Abena Gyasiwaa  Marketing Manager 2017-01-12  08:07:00  17:40:00  9.55

Notes:
If you have an overnight shift, it should calculate the hours correctly, but the WorkingDate column will be the date that the shift started.

If an employee checks out without checking in first, the check out will be dropped.

If an employee checks out twice without checking in between the two events, the later check out will be dropped.

If an employee has checked in and hasn't checked out again yet, you will get Null in the CheckOut and Hours columns.

If an employee checks in twice without checking out between the two events, they are assumed to have checked out and back in at the same time.
Eg: (Time In at 08:07, Time In at 08:37, Time Out at 17:40) will produce (In 08:07, Out 08:37, 0.5 Hours) and (In 08:37, Out 17:40, 9.05 Hours)
If you would rather return Null in the CheckOut and Hours columns, you can add And O.IsTimeOut = 1 to the LEFT JOIN conditions:

SQL

FROM
    cteDates As I
    LEFT JOIN cteDates As O
    ON O.EmployeeID = I.EmployeeID
    And O.RN = I.RN + 1
    And O.IsTimeOut = 1

Calculating checkin and checkout for employee attendance records.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0