Multiple Row into Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple Row into Columns

Author

Topic

ejbatu
Starting Member

21 Posts

Posted - 2010-05-07 : 14:31:10

Hi,

What I need to do next is basically, if you take the person 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.

Name |EE # |State |First OutPunch |Second In Punch |Duration
John Smith |123456789 |GA |4/19/2010 10:32 |NULL |NULL
John Smith |123456789 |GA |4/19/2010 19:05 |NULL |NULL
John Smith |123456789 |GA |NULL |4/19/2010 19:33 |NULL
John Smith |123456789 |GA |4/21/2010 13:06 |NULL |NULL
John Smith |123456789 |GA |NULL |4/21/2010 13:38 |NULL

Would like the data to look like this:
Name |EE # |State |First OutPunch |Second In Punch |Duration
John Smith |123456789 |GA |4/19/2010 7:05 PM |4/19/2010 7:33 PM |28
John Smith |123456789 |GA |4/21/2010 1:06 PM |4/21/2010 1:38 PM |32

Thanks,

Ej

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-07 : 14:41:44

[code]SELECT t.Name,
t.[EE #],
t.[State],
t1.[FirstOutPunch],
t.SecondInPunch
DATEDIFF(minute,t1.[FirstOutPunch],t.SecondInPunch) AS Duration
FROM Table t
CROSS APPLY (SELECT MAX(FirstOutPunch) AS FirstOutPunch
FROM Table
WHERE Name = t.Name
AND [EE #] = t.[EE #]
AND State = t.State
WHERE FirstOutPunch < t.SecondInPunch
)t1
WHERE SecondInPunch IS NOT NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ejbatu
Starting Member

21 Posts

Posted - 2010-05-10 : 14:45:51

This worked great! Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 14:53:49

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ejbatu
Starting Member

21 Posts

Posted - 2010-05-11 : 13:22:24

The script provided works great for the original scenario I provided. I just run into a scenario like below:

Name |EE # |State |First OutPunch |Second In Punch |Duration
John Smith |123456789 |GA |4/19/2010 19:05 |NULL |NULL
John Smith |123456789 |GA |NULL |4/19/2010 19:33 |NULL
John Smith |123456789 |GA |NULL |4/19/2010 20:32 |NULL
John Smith |123456789 |GA |4/21/2010 13:06 |NULL |NULL
John Smith |123456789 |GA |NULL |4/21/2010 13:38 |NULL

So I would like the data to look like this, where the 20:32 is ignored:
John Smith |123456789 |GA |4/19/2010 7:05 PM |4/19/2010 7:33 PM |28
John Smith |123456789 |GA |4/21/2010 1:06 PM |4/21/2010 1:38 PM |32

What is the best way to edit the script provided to accommodate the scenario above?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 11:27:18

[code]
SELECT t.Name,
t.[EE #],
t.[State],
t1.[FirstOutPunch],
t.SecondInPunch
DATEDIFF(minute,t1.[FirstOutPunch],t.SecondInPunch) AS Duration
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY Name,[EE #],State ORDER BY SecondInPunch ASC) AS Seq
FROM
Table WHERE SecondInPunch IS NOT NULL) t
CROSS APPLY (SELECT MAX(FirstOutPunch) AS FirstOutPunch
FROM Table
WHERE Name = t.Name
AND [EE #] = t.[EE #]
AND State = t.State
WHERE FirstOutPunch < t.SecondInPunch
)t1
WHERE t.Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ejbatu
Starting Member

21 Posts

Posted - 2010-05-12 : 14:16:43

Thank you for getting back to me on this.
Okay, sorry for not giving you the complete detail... The code provided only brings up one item per person instead of multiple record per person. Here a sample of data (separated by pipe |) that I’m working with (TableA and TableB). Basically, 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

[CODE]
TableA
Name|EE#|Event DT|In Punch|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#|Event DT|In Punch|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

[/CODE]

ejbatu
Starting Member

21 Posts

Posted - 2010-05-14 : 16:47:51

Any word on this?... thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-15 : 03:46:50

ok. here you go (till the next set of sample data from you with new rules!)


SELECT tb.Name,
tb.[EE#],
tb.State,
t1.OutPunch,
tb.[In Punch],
DATEDIFF(minute,t1.OutPunch,tb.[In Punch]) AS Duration,
tb.State
FROM TableA tb
CROSS APPLY (SELECT TOP 1 OutPunch
             FROM TableA
             WHERE Name= tb.Name
             AND [EE#] = tb.[EE#]
             AND [Event DT]=tb.[Event DT]
             AND OutPunch < tb.[In Punch]
             AND (Reason='New' OR Reason LIKE 'break%')
             ORDER BY OutPunch DESC
            )t1
INNER JOIN tableB t
ON t.Name = tb.Name
AND t.[EE#] = tb.[EE#]
AND t.[In Punch] = tb.[In Punch]
AND t.[OutPunch] = tb.[OutPunch]
WHERE tb.Reason LIKE 'break%'

for the other values add corresponding fields ( i cant make out correct values due to skewed format posted)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ejbatu
Starting Member

21 Posts

Posted - 2010-05-19 : 08:16:20

No other sample data... this did it. Thank you for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources