Click here to Skip to main content
15,303,572 members
Please Sign up or sign in to vote.
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
Updated 7-May-20 8:08am
v2
Comments
NanaKwame 4-May-20 16:54pm
   
@Richard MacCutchan please it did not resolve it. Thanks anyway.

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.
   
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
   
Comments
NanaKwame 12-May-20 20:11pm
   
@Richard Deeming thanks for the effort. I appreciate the time you spent to help me on this. I run it and it works. I later also came out with this solution.

Create Proc prcEmployeeAttendanceRecords
(
@SalesDate DateTime
)
As
Begin
Insert Into EmployeeAttendanceRecords

Select
t.EmployeeID,
t.EmployeeName,
@SalesDate
, CheckIn = CONVERT(VARCHAR(10), t.CheckIn, 108)
, CheckOut = CONVERT(VARCHAR(10), t.CheckOut, 108)
, [Hours] = CAST(DATEDIFF(MINUTE, t.CheckIn, t.CheckOut) / 60. AS DECIMAL(10,2))
From(
Select
t.EmployeeID,
t.EmployeeName,
t.WorkingDate
, CheckIn = t.TimeInput
, CheckOut = r.TimeInput
, RowNum = ROW_NUMBER() OVER (PARTITION BY t.EmployeeID, r.TimeInput ORDER BY 1/0)
From EmployeeAttendanceDetails t
OUTER APPLY (
SELECT TOP 1 *
From EmployeeAttendanceDetails t2
WHERE t2.EmployeeID = t.EmployeeID
AND t2.TimeInput > t.TimeInput
AND DATEADD(dd, 0, DATEDIFF(dd, 0, t.TimeInput)) = DATEADD(dd, 0, DATEDIFF(dd, 0, t2.TimeInput))
AND t2.InputType = 'Time Out'
And t2.WorkingDate = @SalesDate
ORDER BY t2.TimeInput
) r
WHERE t.InputType = 'Time In'
) t
WHERE t.RowNum = 1
End

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