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?

Improve company productivity with a Business Account.Sign Up

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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.