Multiple Row Into Columns

Question

Multiple Row Into Columns

EjSQLme

SSCrazy

Points: 2172
More actions
May 17, 2010 at 12:06 pm

#219014

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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Hi there, i am slowly understading your requirement.. got a doubt here, will there be only 3 entries per person per day?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

And as our Jeff said, please go through the article in the first link of his signature line! 🙂