Solved

SQL 2000 - 2 records combine to 1 record

Posted on 2009-05-08
6
207 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 18

Expert Comment

by:UnifiedIS
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you for your help
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help on Setting up an identical test database 17 50
SQL Query Syntax Error 9 30
encyps queries mssql 15 26
sql calculate averages 18 24
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

12 Experts available now in Live!

Get 1:1 Help Now