In our database(SQL Server 2012) we are having Shift Master and Attendance Punch Table.
I need to calculate attendance for an employee based on shift timings and employee punch time.
Shift Master Table has Shift_Id, Shift_Name, Start_Time, End_Time.
Attendance Punch Table has Employee_Code(int), Punch_Date(Small DateTime), Punch_Time(Small DateTime).
Shift Table: Shift_Id Shift_Name Start_Time End_Time 1 Shift-1 09:00 18:00 2 Shift-2 16:30 01:00 3 Shift-3 20:00 04:00 Attendance Punch Table Employeecode Date PunchTime 1209 01/09/2020 09:01 1236 01/09/2020 08:55 1298 01/09/2020 16:30 1209 01/09/2020 18:32 1236 01/09/2020 18:10 2175 01/09/2020 20:01 1298 02/09/2020 01:15 2175 02/09/2020 04:00
If Employee Come in Shift-1 Both In and Out Time on same day, total hours can be calculated, but I have issue in calculating for Employee Punch In on one day and Punch Out on another day(For example employee comes to shift-2 or shift-3)
I want system to assign shift automatically based on punch time. Employee may come in all three shifts. For example, If employee punch on (08:30 or 09:00 or 09:20 or 11:00) means system should assign shift as Shift-1, employee can apply permission for 2hrs(09:00 to 11:00).
I tried with SQL Stored Procedure to achieve this.
It comes perfectly for Punch In and Out occurs on same day, my query fails to retrieve for Punch In and Out falls on different days and not able to calculate total working hours.