Number of entry query.

MSSQL2K
SP4

Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs.

--drop table #foo
create table #foo
(empID int,
transDate datetime,
transType varchar(10))

insert into #foo values(1, '01/01/06 01:01:01','Insert')
insert into #foo values(1, '01/01/06 01:01:02','Update')
insert into #foo values(1, '01/01/06 01:01:03','Delete')
insert into #foo values(2, '01/01/06 01:01:01','Insert')
insert into #foo values(2, '01/01/06 01:01:02','Update')

select f.empID, Change =
(select count(transDate) from #foo f2
where f2.empID = f.empID
group by empID),
f.transDate, f.transType
from #foo f

Current results:

1 3 2006-01-01 01:01:01.000 Insert
1 3 2006-01-01 01:01:02.000 Update
1 3 2006-01-01 01:01:03.000 Delete
2 2 2006-01-01 01:01:01.000 Insert
2 2 2006-01-01 01:01:02.000 Update

Desired results:

1 1 2006-01-01 01:01:01.000 Insert
1 2 2006-01-01 01:01:02.000 Update
1 3 2006-01-01 01:01:03.000 Delete
2 1 2006-01-01 01:01:01.000 Insert
2 2 2006-01-01 01:01:02.000 Update

TIA, CFRwhere f2.empID = f.empID AND f2.transdate <= f.transdate

Which presumes no 2 can have the same date/time value.|||So close, yet so far. Thanks.|||Sorry, does that mean the answer is close but not correct? It produces the desired result on the sample data.|||Sorry, does that mean the answer is close but not correct? It produces the desired result on the sample data.
Nah, I am pretty sure he means that until your assist, CFR was so closer and yet so far from their solution.

After all, they would have asked for more help other wise, or said what they were getting wrong.

I guess we should be glad that they at least wrote back to thank you. Some folks get the answer, and then disappear.|||As Code Carpenter mentioned, I was so close yet so far. The code did exactly what I needed. As far as disappearing, Im afraid you folks are stuck with me for a while.

Thanks again!