Renumbering Invoice Nos. through query ?

How can I renumber the following Invoice Nos. through Query.
After sorting by date, the 1st invoice no. to be started with 2006050001
Current Records in table
Before Query
InvNo Date
2006050133 12-MAY-06
2006050134 12-MAY-06
2006050135 12-MAY-06
2006050136 15-MAY-06
2006050137 04-MAY-06
2006050138 03-MAY-06
2006050139 03-MAY-06
2006050140 03-MAY-06
2006050141 03-MAY-06
2006050142 03-MAY-06
2006050143 03-MAY-06
After Query
InvNo Date
2006050001 03-MAY-06
2006050002 03-MAY-06
2006050003 03-MAY-06
2006050004 03-MAY-06
2006050005 03-MAY-06
2006050006 03-MAY-06
2006050007 04-MAY-06
2006050008 12-MAY-06
2006050009 12-MAY-06
2006050010 12-MAY-06
2006050011 15-MAY-06
Any idea please ?
Best Regards,
LuqmanCreate a new table with
Select InvNo ,Date Into NewTable From OriginalTable Order By Date -- I hope
you have more field to use in order by since the date is repeating
Then goto newtable and add a new Identity Field as ID
Now you should have
New Table
ID InvNo Date
1 2006050138 03-MAY-06
2 2006050139 03-MAY-06
3 etc
4
5
Now
you can update your original table by joining with new table and and
formating your new InvNo using ID field.
hope this helps,
erdal
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OjrLclVhGHA.4404@.TK2MSFTNGP05.phx.gbl...
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>|||create table invoice_sam(
InvNo varchar(25),
Date smalldatetime)
insert into invoice_sam values('2006050133','12-MAY-06')
insert into invoice_sam values('2006050134','12-MAY-06')
insert into invoice_sam values('2006050135','12-MAY-06')
insert into invoice_sam values('2006050136','15-MAY-06')
insert into invoice_sam values('2006050137','04-MAY-06')
insert into invoice_sam values('2006050138','03-MAY-06')
insert into invoice_sam values('2006050139','03-MAY-06')
insert into invoice_sam values('2006050140','03-MAY-06')
insert into invoice_sam values('2006050141','03-MAY-06')
insert into invoice_sam values('2006050142','03-MAY-06')
insert into invoice_sam values('2006050143','03-MAY-06')
select identity(int,1,1) as sno,
invno,
date
into #step1
from invoice_sam
order by date asc
select substring(invno,1,6) +
case when sno between 1 and 9 then '000' + convert(varchar(5),sno)
when sno between 10 and 99 then '00' + convert(varchar(5),sno)
when sno between 100 and 999 then '0' + convert(varchar(5),sno)
else convert(varchar(5),sno)
End as invno,
date
from #step1
Regards
Sudarshan Selvaraja
"Luqman" wrote:

> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||I hope you want the number to start from 1 for each month ..
You can try this Query
UPDATE table1 SET InvNum =
LEFT(CONVERT(VARCHAR,InvDate,112),6) +
REPLACE
(
STR(
(SELECT
COUNT(*)
FROM
Table1 t2
WHERE
t2.Num1 <= Table1.Num1
AND YEAR(Table1.InvDate) = YEAR(t2.InvDate) AND MONTH(Table1.InvDate) =
MONTH(t2.InvDate)
)
,4),
' ','0')
- Sha Anand
"Luqman" wrote:

> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||A bit simpler, (not requiring a temporary table)
select left(s1.InvNo,6) +
right('0000'+
cast((select count(*)
from invoice_sam s2
where s2.Date < s1.Date
or (s2.Date = s1.Date
and s2.InvNo <= s1.InvNo)) as varchar(4)),4) as
InvNo,
Date
from invoice_sam s1
order by 1