April 21, 2008 at 12:47 pm
I need to select the first occurance of each employee, entrydate, and entrytime
Here is what I have:
1 table called: entries
3 columns called: employee, entrydate, entrytime
Example of what it looks like when all are selected (there are a total of 3900 rows):
Employee Entrydate Entrytime
Scot 4/18/2008 6:05am
Jenna 4/18/2008 7:00am
Mike 4/18/2008 5:30am
Scot 4/18/2008 7:00am
Brandon 4/19/2008 6:00am
Scot 4/19/2008 6:07am
For each employee i need to select the first time for each date and display it. How can I do this?
April 21, 2008 at 12:53 pm
SELECT Employee, Entrydate, MIN(Entrytime) AS Entrytime
FROM Entries
GROUP BY Employee, Entrydate
April 21, 2008 at 12:56 pm
Of course, now that I am looking at your sample data a bit, it looks like your time column is probably a VARCHAR in which you could have 8:00am and 2:00pm - in which case you will get the MIN of these which is actually the later of the two times.
If this is the case, I would first suggest storing times in a better format. Otherwise, you will need to convert the time into a DATETIME (CONVERT(DATETIME,'2:00pm')).
April 21, 2008 at 1:11 pm
Something along these lines will work as well. I agree you should fix your data types. This will only work well if you've got good indexes too.
SELECT t.Name, t.Date, t.Time
FROM Table t
JOIN (SELECT TOP 1 Name, Date, Time
FROM Table t2
WHERE t2.Name = t.Name
ORDER BY Date DESC, Time DESC) AS t2
ON t.name = t2.name and t.date = t2.date and t.time = t2.Time
You can also try using MAX. I've seen either one perform better. There's also a way to do this using ROW_NUMBER().
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2008 at 11:57 am
Thank you so much. This helped and worked exactly how I need it.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply