Multiple Row Into Columns

[ejbatu]

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

[Delerna]

Something Like this
[code=sql]
SELECT Name,[EE #],State,convert( datetime,left(m ax([First OutPunch]),11)
max([First OutPunch]) as [First OutPunch],
max([Second In Punch]) as [Second In Punch],
datediff(s,max([First OutPunch]),max([Second In Punch])
FROM
(
SELECT Name,[EE #],State,
convert(datetim e,left(isNull([First OutPunch],[Second In Punch]),11) as Dte,
[First OutPunch],
[Second In Punch]
FROM theTable
)a
GROUP BY Name,[EE #],State,Dte


[/code]

it's not tested

[ejbatu]

This worked great! Thank you

[ejbatu]

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?

[Delerna]

Unless I am miss-understanding the question

In the first sample when 2 records existed the query needed to return the record with the latest date

In this sample the query needs to return the earliest date.

So the query needs to
sometimes return the latest
and
othertimes the earliest date


.....based on what ???


There must be some fact in the data that a query can use to make that decision. If such a fact does not exist in the data then it is not possible to do that.

How will the query know which one to choose?

[ejbatu]

Okay, sorry for not giving you the complete detail… 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

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

[ejbatu]

Any word on this?... thanks!

[Delerna]

That data is a whole different scenario.
I took a look at the data and have been thinking upon your
problem when I have time.

It's not as simple as the scenario you painted in the orriginal post and I don't come here and I don't have time, to fill orders for free queries.
Rather I come here to assist people to overcome difficulties they may be experiencing writing their own solutions.

I don't know whether you have control over the design of the database but the tables, the way they are structured do not lend themselves to reliably query what you need.
For example,
Why are there 2 tables with the data in
Name|EE#|Event DT|In Punch|OutPunch
duplicated in both?
From what I can see there should only be 1 table
with these fields
[code=sql]
Name
EE#
Event DT
PunchTime
PunchType IN or OUT
State
Reason
Client
User
FuncCode
[/code]
I don't suggest that this is absolutely how the table should look, I cannot see your database as a whole. This is merely a very simple example of how those two tables could be normalised and simplified.

Your query would then have been a matter of selecting
the punchtime where punchtype=out and reason= breakA or breakB and then matching that to the very next following record where punchtype=in


If you must keep the tables as they are then you need a query to massage that data into a form that looks somthing like the table I suggested. (It's a messy solution to improper normalization (see normalization-table-structures) and if you take this route you will probably find yourself taking this solution again and again further down the track)

I tend to write queries in stages progressively reaching the result I need.
Do that and if you have a difficulty getting some part of the query to work then post the query along with a clear description of the problem and we can assist you (not do it for you).

[ejbatu]

Okay, that is a good challenge. I'll work on it and get back to you... Thanks.