Interesting 2

[act2]

This is an expansion of Interest problem

Well the idea is that the program is reading data from many
digital clocks which are position from many Location, the guys in the company, of course, use to pass their cards every time they enter the building. Some time they pass more than once (only hours and minutes are recorded ). So when I get the lists from the clocks, there is data like

EMPLOYEE DATE TIME EntryLocation
marcus 01/01/01 09:01 1
marcus 01/01/01 09:01 1
marcus 01/01/01 13:00 1
marcus 01/01/01 16:00 3
Floyd 01/01/01 09:02 1
Petrus 01/01/01 09:03 1
Casey 01/01/01 09:04 2
Casey 01/01/01 09:04 2
Casey 01/01/01 12:04 1
Casey 01/01/01 16:04 1

----

What I need is to get earliest data for each employee incluging EntryLocation.

eg Output:

EMPLOYEE DATE TIME EntryLocation

marcus 01/01/01 09:01 1
Floyd 01/01/01 09:02 1
Petrus 01/01/01 09:03 1
Casey 01/01/01 09:04 2

I could use cursor and temporary table to get the desired results.

Is there any better ways?

Ta

 

[dbtech]

Hi
hope i got it right!

Note replace x1 with your Table name, and also checkdate column.

/*create table x1
employee char(10),
Checkdate datetime,
EntryLocation tinyint)
*/

SELECT  x1.employee,
Checkdate,
EntryLocation 
FROM x1
INNER JOIN 
(SELECT employee,MIN(checkdate) StartDate
            From x1 
            group by employee) y
on  x1.employee=y.employee
where x1.checkdate=y.StartDate
group by x1.employee,x1.Checkdate,x1.EntryLocation

if you run the above query u should have the results.

dbtech