Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL 2000 - 2 records combine to 1 record

Posted on 2009-05-08
6
214 Views
Last Modified: 2012-08-14
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?
0
Comment
Question by:wsadfilm
  • 2
  • 2
  • 2
6 Comments
 
LVL 12

Accepted Solution

by:
Dimitris earned 500 total points
ID: 24335691
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
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 24335709
I would suggest not displaying the shifts like that.  What happens if someone punches out for 2 breaks during one day?
0
 
LVL 12

Expert Comment

by:Dimitris
ID: 24335750
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 18

Expert Comment

by:UnifiedIS
ID: 24335929
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
 

Author Comment

by:wsadfilm
ID: 24338743
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
 

Author Closing Comment

by:wsadfilm
ID: 31579398
Thank you for your help
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question