how to get total working hours per day?

Question

Hello Everyone how to get total working hours per day with respect to a HolderName using sql query? This is the sql table that contain input/output date and time with input/output status. Kindly answer me. Thanks advance. ID CardNo HolderNo HolderName IODate IOTime IOGateNo IOGateName IOStatus 1 118 85 Raj 1/1/2016 12:14PM 1-Jan Door #1(I/O) Entry 2 118 85 Raj 1/1/2016 12:14PM 2-Jan Door #2(I/O) Entry 3 118 85 Raj 1/1/2016 12:14PM 6-Jan Door #2(I/O) Exit 4 118 85 Raj 1/1/2016 12:15PM 2-Jan Door #2(I/O) Entry 5 118 85 Raj 1/1/2016 4:05PM 6-Jan Door #2(I/O) Exit 6 118 85 Raj 1/1/2016 4:05PM 5-Jan Door #1(I/O) Exit 7 118 85 Raj 1/1/2016 4:08PM 1-Jan Door #1(I/O) Entry 8 118 85 Raj 1/1/2016 4:08PM 2-Jan Door #2(I/O) Entry 9 118 85 Raj 1/1/2016 4:09PM 6-Jan Door #2(I/O) Exit 10 118 85 Raj 1/1/2016 4:09PM 5-Jan Door #1(I/O) Exit

Nitin Sontakke · Answer

Honestly I never understood the SELECTs like the one you have written. I am not sure how you are joining the CardNo in the records being selected. Personally I don't believe that you will be able to achieve it in single SELECT. For clarity you are better off writing a cursor.

Nitin Sontakke · Answer

For every person if you sort on IODate and IOTime there have to be even number of IOStatus entries for Entry and Exit. Furthermore they need to be sequential. For example you will need Entry - Exit Entry - Exit. Not like Entry - Entry - Exit or Entry - Exit - Exit. If you have entries like that you need to choose which record to ignore. With the help of cursor you will need to create another table (may be temp table) as follows: CardNo IODate EntryTime ExitTime When you have table like this in select you can have datediff of minutes and then you can sum that column to get each employees effective time in the office. Hope it helps!

Midhun T P · Answer

Hi Can you please specify what does column IODate & IOGateNo means? You want total hours in a day based on which date?

Mani Kandan · Answer

Hello Nitin Do you think like this? is this correct? CREATE TABLE #emp_time ( CardNo varchar(50) IODate datetime EntryTime datetime ExitTime datetime ) insert into #emp_time (CardNoIODateEntryTimeExitTime) select top 10 A.CardNoA.IODate( select top 10 CAST(A.IOTime as datetime) - CAST(A.IOTime AS datetime) as EntryTime from Tble_IOData A where IOStatus ='Entry') (select top 10 CAST(A.IOTime as datetime) - CAST(A.IOTime AS datetime) as ExitTime from Tble_IOData A where IOStatus ='Exit') from Tble_IOData A

Mani Kandan · Answer

Hi Midhun Thank you for replay me. IODate is the employee worked date and IOGateNo is the punching machine door or gate number. I want total hours in a day based on IOTime with respect to IODate. Kindly answer me

ID	CardNo	HolderNo	HolderName	IODate	IOTime	IOGateNo	IOGateName	IOStatus
1	118	85	Raj	1/1/2016	12:14PM	1-Jan	Door #1(I/O)	Entry
2	118	85	Raj	1/1/2016	12:14PM	2-Jan	Door #2(I/O)	Entry
3	118	85	Raj	1/1/2016	12:14PM	6-Jan	Door #2(I/O)	Exit
4	118	85	Raj	1/1/2016	12:15PM	2-Jan	Door #2(I/O)	Entry
5	118	85	Raj	1/1/2016	4:05PM	6-Jan	Door #2(I/O)	Exit
6	118	85	Raj	1/1/2016	4:05PM	5-Jan	Door #1(I/O)	Exit
7	118	85	Raj	1/1/2016	4:08PM	1-Jan	Door #1(I/O)	Entry
8	118	85	Raj	1/1/2016	4:08PM	2-Jan	Door #2(I/O)	Entry
9	118	85	Raj	1/1/2016	4:09PM	6-Jan	Door #2(I/O)	Exit
10	118	85	Raj	1/1/2016	4:09PM	5-Jan	Door #1(I/O)	Exit