SQL 2000 - 2 records combine to 1 record

I have a query that returns timecard punches from a SQL 2000 database.

LastName      FirstName      PunchDate      PunchIn      PunchOut

Smith            Joe            5/7/09            08:00            12:00
Smith            Joe            5/7/09            13:00             16:00
Jones            Bob            5/7/09            08:00            16:00

I have 2 records for Joe because he punched out for a lunch break.  I would like to show the results in 1 line

LastName      FirstName      PunchDate      PunchIn      BreakOut      BreakIn      PunchOut      

Smith            Joe            5/7/09            08:00            12:00            13:00            16:00
Jones            Bob            5/7/09            08:00            NULL            NULL            16:00

Any thoughts?
wsadfilmAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
DimitrisConnect With a Mentor Senior Solution ArchitectCommented:
here u r
SELECT  LastName,FirstName,PunchDate, 
	min(PunchIn) as PunchIn, 
	case WHEN min(PunchOut)<>max(PunchOut) then min(PunchOut) ELSE NULL END as BreakOut,
	case WHEN min(PunchIn)<>max(PunchIn) then max(PunchIn) ELSE NULL END as BreakIn,		
	max(PunchOut) as PunchOut
FROM MyTable
group by LastName,FirstName,PunchDate

Open in new window

0
 
UnifiedISCommented:
I would suggest not displaying the shifts like that.  What happens if someone punches out for 2 breaks during one day?
0
 
DimitrisSenior Solution ArchitectCommented:
it is for exactly what it asks.
2 records combine to 1 record, not three or more, it's a limitation of this approach.
In case there are more that three records then it will display the first time he exit the office and the last time of entrance.  
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
UnifiedISCommented:
dankangr,
yes, it's limited, that's why I suggested revisiting the plan to display the shifts in that manner.  I wasn't suggesting that your solution would not meet the requirements posed, simply that the requirements may be flawed.
0
 
wsadfilmAuthor Commented:
I tested the above code and it does give the desired results.  For this situation 99% of the time I am working with the example provided (1 or 2 records per day per employee).  There is that 1% of the time when there may be more than 2 records.

I just have a little more testing to do to be sure.

Thanks
0
 
wsadfilmAuthor Commented:
Thank you for your help
0
All Courses

From novice to tech pro — start learning today.