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.