Auto fill collumn

I have an existing collumn in a table that contains a 5 digit number used as
an employee id field.(datatype nvarchar) I would like to auto fill the rows
that are '00000' or Null with an number beginning with X0001 and incrementin
g
by 1 thereafter.( I want the X as a prefix). Any Ideas'
Thanks, RobOn Mon, 31 Jan 2005 13:37:23 GMT, Robb Mann wrote:

> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the ro
ws
>that are '00000' or Null with an number beginning with X0001 and incrementi
ng
>by 1 thereafter.( I want the X as a prefix). Any Ideas'
>Thanks, Rob
Hi Rob,
Define: "beginning" and "thereafter". Remember that tables are UNordered
sets of data, by definition. There is no "first" or "beginning" row. You
will have to define some ranking to base your numbers on.
Assuming you want to use the column date_entered to base the ranking, and
assuming you have no diuplicates in the date_entered, you could use the
following:
UPDATE MyTable
SET employee_id = 'X' + LEFT ('0000' +
CAST((SELECT COUNT(*)
FROM MyTable AS a
WHERE a.date_entered <= MyTable.date_entered
AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
WHERE COALESCE (employee_id, '00000') = '00000'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is the primary key of the table? SSN? Payroll number? Are you
saying that the table doesn't have a key right now? If you just want an
arbitrary surrogate key then use IDENTITY. If you requirements are more
complex then we'll need some more information: DDL, sample data,
required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||If your table does not have duplicated employees by first name and last name
,
then you can use this approach (if not, use also the department to calculate
the rank):
use northwind
go
create table t (
empid char(5) null,
fname varchar(25) not null,
lname varchar(25) not null
)
insert into t values ('00000', 'a', 'b')
insert into t values ('00000', 'c', 'd')
insert into t values (null, 'e', 'f')
insert into t values ('00001', 'v', 'w')
select * from t order by fname, lname
update
e
set
e.empid = 'x' + right('000' + ltrim(rank), 4)
from
t as e
inner join
(
select
a.fname,
a.lname,
count(*) as rank
from
t as a
inner join
t as b
on b.fname + b.lname <= a.fname + a.lname
and (a.empid is null or a.empid = '00000')
and (b.empid is null or b.empid = '00000')
group by
a.fname,
a.lname
) as f
on e.fname = f.fname and e.lname = f.lname
select * from t order by fname, lname
drop table t
go
AMB
"Robb Mann" wrote:

> I have an existing collumn in a table that contains a 5 digit number used
as
> an employee id field.(datatype nvarchar) I would like to auto fill the r
ows
> that are '00000' or Null with an number beginning with X0001 and increment
ing
> by 1 thereafter.( I want the X as a prefix). Any Ideas'
> Thanks, Rob
>|||On Mon, 31 Jan 2005 14:54:43 +0100, Hugo Kornelis wrote:

>UPDATE MyTable
>SET employee_id = 'X' + LEFT ('0000' +
> CAST((SELECT COUNT(*)
> FROM MyTable AS a
> WHERE a.date_entered <= MyTable.date_entered
> AND COALESCE(a.employee_id,'00000') = '00000') AS int), 4)
>WHERE COALESCE (employee_id, '00000') = '00000'
(snip)
Correction: I should have used RIGHT, not LEFT.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Auto counting in SQL

Hi all,

I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it.

So if I for example select all Dates older than today's date, I would want something like this:

110/12/2006210/18/2006310/20/2006410/22/2006510/30/2006

Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually".

Is this possible? And if yes, how ? :)

Thanks in advance

Nick

See if this helps:

Declare @.TTable ( col1datetime)Insert into @.TSelect'10/12/2006'unionallSELECT'10/18/2006'unionallSELECT'10/20/2006'unionallSELECT'10/22/2006'unionallSELECT'10/30/2006'SELECTCOUNT(*)AS [Row Number], T1.col1FROM @.T T1, @.T T2WHERE T1.col1 >= T2.col1Group by T1.col1

|||

SELECT *,row_number() OVER (ORDER BY ...) AS [Row Number]

FROM ...

ORDER BY ...

Just fill in the ...'s

|||

ndinakar, thanks for the answer, but not quite what i was looking for

Motley, thanks :) that was exactly what I needed ;)

cheers!

|||

I still don't get it how does this work ... an example:

If I return the values from DB using the following SQL

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTable) AS DerivedTable

it works perfectly. When I add some filtering to WHERE-part

WHERE MyColB <> 'value1' AND MyColB <> 'value2'

I get somewhat strange results. Row numbering in my case starts from row 57, second row jumps to 60, then go normally for couple of rows, then jumps to 77 and so on. So it's definately not sequential. Any ides for this ?

|||

You should make sure that your where is in the correct SELECT statement.
There is for example a big difference in results when used either

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTable) AS DerivedTableWHERE MyColB <> 'value1' AND MyColB <> 'value2'

in stead of

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTableWHERE MyColB <> 'value1' AND MyColB <> 'value2') AS DerivedTable

in the first statement, you're likely to get non-sequential numbering, and in the second one you should get them sequentially

|||

Yes, of course, I didn't realize that, but now it works. Thanks