Update Field With AutoNumber Based On HiredDate In Ascending Order 1

[Happy2]

Hi everyone,

Please help.

I need to update my NewMgrID in tblManager table. What I need to do is group the records in
tblManager by State, then within each State, sort them by HiredDate in ascending order. I have
to update the NewMgrID starting with the record that has a smallest HiredDate (e.g. Manager
with ID = 10 in State of AR). Then assign that record starting with 01 and the next record
with 02, and so on (by adding 1 unit to the next smallerst HiredDate). I don't know how to
increase the number in NewMgrID field with the ascending order of HiredDate.

Can anyone help please. Thanks for your help in advance!


The table should look like this after assiging NewMgrID:

ManagerID State HiredDate NewMgrID

10 AR 1/1/00 01
11 AR 1/20/02 03
12 AR 1/22/00 02

13 CA 1/2/02 01
14 CA 1/4/02 02
15 CA 1/10/03 03

 

[ClaireHCM]

select f1.Managerid,
f1.state,
f1.hireddate,
'NewID' =
case
when state = state then '0'+ cast(( select count(*)+1
from pen f2
where f2.state = f1.state
and f2.hireddate < f1.hireddate) as varchar(2))
end
from pen f1

 

[Happy2]

After running to stored procedure, I got NewID from 01 to 06.
However, I need to get NewID from 01 to 03 for AR state and
from 01 to 03 again for CA state. Can I get help another time?

Thanks,

 

[ClaireHCM]

why?I got 01-03 for ar and 01-03 ca
Did you change anything in the query?

 

[ClaireHCM]

This is how I simulated your table

create table pen (ManagerID int ,State varchar(2),Hireddate datetime)
insert into pen select 10,'AR','1/1/00'
insert into pen select 11,'AR','1/20/02'
insert into pen select 12,'AR','1/22/00'
insert into pen select 13,'CA','1/2/02'
insert into pen select 14,'CA','1/4/02'
insert into pen select 15,'CA','1/10/03'

then run this query,it works well

select f1.Managerid,
f1.state,
f1.hireddate,
'NewID' =
case
when state = state then '0'+ cast(( select count(*)+1
from pen f2
where f2.state = f1.state
and f2.hireddate < f1.hireddate) as varchar(2))
end
from pen f1

 

[Happy2]

hi ClaireHsu,

No I didn't change anything. I rerun the stored procedure and still got:
01 for ManagerID 10
05 for ManagerID 11
02 for ManagerID 12
03 for ManagerID 13
04 for ManagerID 14
06 for ManagerID 15

 

[ClaireHCM]

Stored procedure?Which stored procedure?

Did you do the steps I posted and see?

Please run the following query first,then go back to your stored procedure

create table pen (ManagerID int ,State varchar(2),Hireddate datetime)
go
insert into pen select 10,'AR','1/1/00'
insert into pen select 11,'AR','1/20/02'
insert into pen select 12,'AR','1/22/00'
insert into pen select 13,'CA','1/2/02'
insert into pen select 14,'CA','1/4/02'
insert into pen select 15,'CA','1/10/03'
go
--then run this query,it works well

select f1.Managerid,
f1.state,
f1.hireddate,
'NewID' =
case
when state = state then '0'+ cast(( select count(*)+1
from pen f2
where f2.state = f1.state
and f2.hireddate < f1.hireddate) as varchar(2))
end
from pen f1
go

Tell me what do you get by running these three steps?

 

[Happy2]

Hi ClaireHsu,

I just did as you said again, but the result is still from 01 to 06. Can you help me check back the code with me? I haven't got it yet.

Thanks a lot for your big help and your valuable time!!!

 

[ClaireHCM]

That's really weired.Because I tested in both sql 7.0 and 2000 both work fine.

May I know your table and rows inserted in the table?
and also the query statement you execute?

 

[Happy2]

Hi ClaireHsu,

I will try tomorrow at work to see how it come out.

This is my table created by your code:
ManagerID State Hireddate
10 AR 1/1/00
11 AR 1/20/02
12 AR 1/22/00
13 CA 1/2/02
14 CA 1/4/02
15 CA 1/10/03

I didn't run any of my code, only yours as followed.

create table pen (ManagerID int ,State varchar(2),Hireddate datetime)
go
insert into pen select 10,'AR','1/1/00'
insert into pen select 11,'AR','1/20/02'
insert into pen select 12,'AR','1/22/00'
insert into pen select 13,'CA','1/2/02'
insert into pen select 14,'CA','1/4/02'
insert into pen select 15,'CA','1/10/03'
go
--then run this query,it works well

select f1.Managerid,
f1.state,
f1.hireddate,
'NewID' =
case
when state = state then '0'+ cast(( select count(*)+1
from pen f2
where f2.state = f1.state
and f2.hireddate < f1.hireddate) as varchar(2))
end
from pen f1
go

 

[ClaireHCM]

So how is the result when you run in your office?

 

[Happy2]

Hi ClaireHsu,

It's still from 01 to 06. However, don't worry, I will try to work on it and will see. For now, I can update 1 state at a time is ok with me.

Thank you so much for your help.

 

[ClaireHCM]

I am so sad to hear that,I tried on 3 different computer ....For all the three I am getting right answer.
like this

10 AR 2000-01-01 00:00:00.000 01
11 AR 2002-01-20 00:00:00.000 03
12 AR 2000-01-22 00:00:00.000 02
13 CA 2002-01-02 00:00:00.000 01
14 CA 2002-01-04 00:00:00.000 02
15 CA 2003-01-10 00:00:00.000 03

I really have no idea what causes your problem.
If you found out.please tell me!

But if you need help about getting result state-by-state,please let me know