May 17, 2010 at 12:06 pm
Hi,
Here a sample of data (separated by pipe |) that Iām working with (TableA and TableB). Basically, I'm trying to capture two scenarios: if you take the person (John Smith) below as an example, from the data below, there are three records for the 19th, so ignore 10:32a and combine 7:05p with 7:33p on the same row and do a duration calculation on another column. Also if the data is two row on the same date, like the 21st, combine the two data and do a calculation for the duration.
The second scenario Iām trying capture is where Jenny Gram on 5/1 punched out at 2:49PM and punched in at 3:25PM, so since there is a break, need duration calculated for that. On other hand, Bob Davis on 4/27 punched out at 6:19pm and punched in at 7:12pm, since it is a new reason, no need to capture it. Hope this makes sense
TableA
Name |EE# |EventDate|InPunch |OutPunch |State|Reason
John Smith|123456789|4/19/2010|4/19/2010 8:00 AM|4/19/2010 10:32 AM|TN|new
John Smith|123456789|4/19/2010|4/19/2010 1:59 PM|4/19/2010 7:05 PM|TN|new
John Smith|123456789|4/19/2010|4/19/2010 7:33 PM|4/19/2010 9:11 PM|TN|break A
John Smith|123456789|4/21/2010|4/21/2010 9:54 AM|4/21/2010 1:06 PM|TN|new
John Smith|123456789|4/21/2010|4/21/2010 1:38 PM|4/21/2010 6:03 PM|TN|break A
Bob Davis|234526854|4/27/2010|4/27/2010 9:59 AM|4/27/2010 2:44 PM|FL|new
Bob Davis|234526854|4/27/2010|4/27/2010 3:23 PM|4/27/2010 6:19 PM|FL|break A
Bob Davis|234526854|4/27/2010|4/27/2010 7:12 PM|4/27/2010 11:15 PM|FL|new
Bob Davis|234526854|4/28/2010|4/28/2010 8:59 AM|4/28/2010 2:32 PM|FL|new
Jenny Gram|345685252|5/1/2010|5/1/2010 10:01 AM|5/1/2010 2:44 PM|CA|new
Jenny Gram|345685252|5/1/2010|5/1/2010 2:45 PM|5/1/2010 2:49 PM|CA|break B
Jenny Gram|345685252|5/1/2010|5/1/2010 3:25 PM|5/1/2010 5:23 PM|CA|break B
Jenny Gram|345685252|5/2/2010|5/2/2010 2:30 PM|5/2/2010 5:38 PM|CA|new
Jenny Gram|345685252|5/2/2010|5/2/2010 6:11 PM|5/2/2010 10:48 PM|CA|break B
TableB
Name |EE# |EventDate|InPunch |OutPunch |InClient |InUser |InFuncCode |OutClient |OutUser |OutFuncCode
John Smith|123456789|4/19/2010|4/19/2010 8:00 AM|4/19/2010 10:32 AM|Download:Device 100052|PunchDevice|P|Download:Device 100052|PunchDevice|P
John Smith|123456789|4/19/2010|4/19/2010 1:59 PM|4/19/2010 7:05 PM|sdfsdfd office|sdfsdfd|E|erterter office|erterter|E
John Smith|123456789|4/19/2010|4/19/2010 7:33 PM|4/19/2010 9:11 PM|Download:Device 100052|PunchDevice|P|Download:Device 100052|PunchDevice|P
John Smith|123456789|4/21/2010|4/21/2010 9:54 AM|4/21/2010 1:06 PM|Download:Device 100052|PunchDevice|P|Download:Device 100052|PunchDevice|P
John Smith|123456789|4/21/2010|4/21/2010 1:38 PM|4/21/2010 6:03 PM|erterter office|erterter|E|Download:Device 100052|PunchDevice|P
Bob Davis|234526854|4/27/2010|4/27/2010 9:59 AM|4/27/2010 2:44 PM|Download:Device 100009|PunchDevice|P|erghjtt office|erghjtt|E
Bob Davis|234526854|4/27/2010|4/27/2010 3:23 PM|4/27/2010 6:19 PM|erghjtt office|erghjtt|E|Download:Device 100009|PunchDevice|P
Bob Davis|234526854|4/27/2010|4/27/2010 7:12 PM|4/27/2010 11:15 PM|sdfsdfd office|sdfsdfd|E|Download:Device 100052|PunchDevice|P
Bob Davis|234526854|4/28/2010|4/28/2010 8:59 AM|4/28/2010 2:32 PM|Download:Device 100009|PunchDevice|P|Download:Device 100009|PunchDevice|P
Jenny Gram|345685252|5/1/2010|5/1/2010 10:01 AM|5/1/2010 2:44 PM|sdfsdfd office|sdfsdfd|E|Download:Device 100009|PunchDevice|P
Jenny Gram|345685252|5/1/2010|5/1/2010 2:45 PM|5/1/2010 2:49 PM|Download:Device 100052|PunchDevice|P|erghjtt office|erghjtt|E
Jenny Gram|345685252|5/1/2010|5/1/2010 3:25 PM|5/1/2010 5:23 PM|Download:Device 100052|PunchDevice|P|Download:Device 100009|PunchDevice|P
Jenny Gram|345685252|5/2/2010|5/2/2010 2:30 PM|5/2/2010 5:38 PM|Download:Device 100052|PunchDevice|P|sdfsdfd office|sdfsdfd|E
Jenny Gram|345685252|5/2/2010|5/2/2010 6:11 PM|5/2/2010 10:48 PM|Download:Device 100052|PunchDevice|P|Download:Device 100052|PunchDevice|P
Output result:
Name |EE# |State |1stOutPunchIdent |2ndInPunchIdent |firstOutPunch|secondInPunch |Duration
John Smith|123456789|TN|erterter|Download:Device 100052|4/19/2010 7:05 PM|4/19/2010 7:33 PM|28
John Smith|123456789|TN|Download:Device 100052|erterter|4/21/2010 1:06 PM|4/21/2010 1:38 PM|32
Bob Davis|234526854|FL|erghjtt|erghjtt|4/27/2010 2:44 PM|4/27/2010 3:23 PM|39
Jenny Gram|345685252|CA|Download:Device 100009|Download:Device 100052|5/1/2010 2:44 PM|5/1/2010 2:45 PM|1
Jenny Gram|345685252|CA|erghjtt|Download:Device 100052|5/1/2010 2:49 PM|5/1/2010 3:25 PM|36
Jenny Gram|345685252|CA|sdfsdfd|Download:Device 100052|5/2/2010 5:38 PM|5/2/2010 6:11 PM|33
May 17, 2010 at 12:25 pm
Can we see what you've tried so far? Also, since you're a newbie, give yourself the best chance at getting a coded answer for your question. Please read the article at the first link in my signature line below.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2010 at 12:27 pm
Hi there, i am slowly understading your requirement.. got a doubt here, will there be only 3 entries per person per day?
May 17, 2010 at 12:28 pm
And as our Jeff said, please go through the article in the first link of his signature line! š
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply