Am I missing something here?
what is wrong with this:
Update o Set mynbr = datediff(d,(select min(mydate) from testt t where t.empid = o.Empid ),o.mydate)
from testt o
Tests:
create table testt (recnbr int , mynbr int, mydate datetime, empid varchar(50))
go
insert into testt (recnbr, mynbr,mydate,empid) Values (1, 0 , '1/1/2001', 'bob')
insert into testt (recnbr, mynbr,mydate,empid) Values (2, NULL , '1/2/2001', 'bob')
insert into testt (recnbr, mynbr,mydate,empid) Values (3, NULL , '1/7/2001', 'bob')
insert into testt (recnbr, mynbr,mydate,empid) Values (4, NULL , '1/8/2001', 'bob')
insert into testt (recnbr, mynbr,mydate,empid) Values (5, NULL , '1/21/2001', 'bob')
insert into testt (recnbr, mynbr,mydate,empid) Values (6, 0 , '8/4/2003', 'cindy')
insert into testt (recnbr, mynbr,mydate,empid) Values (7, NULL , '8/5/2003', 'cindy')
insert into testt (recnbr, mynbr,mydate,empid) Values (8, NULL , '9/1/2003', 'cindy')
insert into testt (recnbr, mynbr,mydate,empid) Values (9, NULL , '9/8/2003', 'cindy')
insert into testt (recnbr, mynbr,mydate,empid) Values (10, NULL, '9/15/2003', 'cindy')
insert into testt (recnbr, mynbr,mydate,empid) Values (11, 0, '3/1/2004', 'terry')
insert into testt (recnbr, mynbr,mydate,empid) Values (12, NULL, ' 3/3/2004', 'terry')
insert into testt (recnbr, mynbr,mydate,empid) Values (13, NULL, ' 3/4/2004', 'terry')
insert into testt (recnbr, mynbr,mydate,empid) Values (14, NULL, ' 3/4/2004', 'terry')
insert into testt (recnbr, mynbr,mydate,empid) Values (15, NULL, '3/20/2004', 'terry')
Update o Set mynbr = datediff(d,(select min(mydate) from testt t where t.empid = o.Empid ),o.mydate)
from testt o
select * from testt
recnbr mynbr mydate empid
------ ----- ----------------------- ------
1 0 2001-01-01 00:00:00.000 bob
2 1 2001-01-02 00:00:00.000 bob
3 6 2001-01-07 00:00:00.000 bob
4 7 2001-01-08 00:00:00.000 bob
5 20 2001-01-21 00:00:00.000 bob
6 0 2003-08-04 00:00:00.000 cindy
7 1 2003-08-05 00:00:00.000 cindy
8 28 2003-09-01 00:00:00.000 cindy
9 35 2003-09-08 00:00:00.000 cindy
10 42 2003-09-15 00:00:00.000 cindy
11 0 2004-03-01 00:00:00.000 terry
12 2 2004-03-03 00:00:00.000 terry
13 3 2004-03-04 00:00:00.000 terry
14 3 2004-03-04 00:00:00.000 terry
15 19 2004-03-20 00:00:00.000 terry
(15 row(s) affected)
* Noel