0

I'm developing an attendance system using VB.NET & SQL server 2008. there is a shifts table with Start & End time of each shift. and there is an employee table. each employee may have one or more shifts. I'm using a log file taken from the fingerprint device. I'm having trouble analyzing the log data which contains the Employee ID, the date & time of the fingerprint, the log data looks like this:

    4   2015-05-02 10:00:09 
    3   2015-05-02 10:24:54 
    4   2015-05-02 14:12:08 
    3   2015-05-02 14:13:32 
    4   2015-05-02 15:46:16 
    7   2015-05-02 18:36:21 
    6   2015-05-02 20:55:05 
    4   2015-05-02 22:00:38 
    3   2015-05-02 22:01:00
    3   2015-05-19 10:13:13
    7   2015-05-19 16:08:02
   11   2015-05-19 17:26:29
   11   2015-05-19 21:25:19
    7   2015-05-19 21:26:07
    3   2015-05-19 21:47:42

I have read the log data and store Employee ID, Date, Time separately in a DataTable. I'm facing many cases & conditions with this data, for example the employee may have forgotten to scan his/her finger upon arrival or leave. The employee may have scanned only after leaving his 1st shift and came in later for his/her 2nd shift. The employee may come in another time other than his regular shift. If The employee has a morning & evening shift, he may scan in the morning and stay until his evening shift is over. and other cases like this.

I want to insert this data into a table like this [EmployeeID, ShiftID, In, Out, Date ] I've been struggling to accomplish this for more than a week.

any advise would help, thank you.

ybloodz
  • 167
  • 2
  • 13
  • It sounds like you can establish the ShiftID by comparing Time with Start and End of Shift. So you get a problem when an employee has only 1 or more than 2 rows per shift? 2 rows implies first is In, second is Out? – Fruitbat Jul 03 '15 at 15:29
  • there is no way of telling if the 1st row in In and the 2nd is Out. two rows could mean that the 1st row is Out of his first shift and the second row is In of his second shift and he did not scan after second shift is over. hope i didn't confuse you. thanks. – ybloodz Jul 03 '15 at 15:35
  • if the only information is user & date/time then manual intervention is required. there is no way for a computer program to catch 100% of all possible combinations in such cases. you may make educated guesses that must be checked afterwards. – Paolo Jul 03 '15 at 15:52
  • yes Paolo, I' figured it needs manual intervention, but i couldn't follow it correctly. it's so confusing to me, so I'm asking for any help or direction that may ease this problem for me. thank you. – ybloodz Jul 03 '15 at 16:09
  • 1
    If there is no way of telling then you have an unsolvable problem. You need to make assumptions before you can insert into the new table. Perhaps you need an intermediate table [EmployeeID, ShiftID, SequenceNo, Time] if you can't determine In and Out. – Fruitbat Jul 03 '15 at 16:10

0 Answers0