attendance time calculation

Question

i have one punched log from biometric machine. how i can calculate the attendance date . please help me

Ganeshamoorthy Arunachalam · Answer

I am using face reader machine. i can get one punch log from that machine. i dont have a flag for separate IN or OUT. i have to show the all records for the person 1.In and Out time 2.Total working Hours 3.Miss Punch logs 4.Night Shift Here I am using Flag. But now i dont have a flag separation . so please help me out create table #final( AttendanceID nvarchar(10),DeviceCode nvarchar(10),MachineNo nvarchar(100), EmployeeCode nvarchar(100),PunchDate smalldatetime,yr char (10),mnth char (10),dy char (10), hr char (10), min char (10),sec char (10), IPAddress varchar (20), Stat char (10)) insert into #final select AttendanceID, DeviceCode, MachineNo, EmployeeCode, PunchDate, Year , Month , Day , Hour , Minute , Second , IPAddress,Stat from Attendance_trial create table #att( EmployeeCode nvarchar(100), Login smalldatetime, LogOut smalldatetime) insert into #att select b1.EmployeeCode,b1.PUNCHDATE as Login, b2.PUNCHDATE as Logout from #final b1, #final b2 where b1.stat = 'I' and b2.EmployeeCode = b1.EmployeeCode and b2.stat = 'O' and b2.PUNCHDATE > b1.PUNCHDATE and b2.PUNCHDATE hour , 24, b1.PUNCHDATE) union all select b1.EmployeeCode,b1.PUNCHDATE, null from #final b1 where b1.stat = 'I' and not exists ( select 1 from #final b2 where b2.EmployeeCode = b1.EmployeeCode and b2.stat = 'O' and b2.PUNCHDATE > b1.PUNCHDATE and b2.PUNCHDATE hour , 24, b1.PUNCHDATE)) union all select b1.EmployeeCode, null , b1.PUNCHDATE from #final b1 where b1.STAT = 'O' and not exists ( select 1 from #final b2 where b2.EmployeeCode = b1.EmployeeCode and b2.STAT = 'I' and b2.PUNCHDATE and b2.PUNCHDATE > DATEADD( hour , -24, b1.PUNCHDATE)) order by employeecode Select #Att.EmployeeCode, --#Att.Login, Min (( convert ( varchar (10), login,111)))Attendance_dt, Min (Login) AS LOGIN, max (logout) as logout From #Att group by #Att.EmployeeCode Drop table #Final Drop table #att

Amit Gupta · Answer

You need to elaborate your question because it is too short to understand/assume what you wanted.