Solved

SQL 2000 - 2 records combine to 1 record

Posted on 2009-05-08
6
209 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

939 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now