SQL Server Performance Forum – Threads Archive

About generating serial number within another fiel

Hi, I have a requirement, where, I want to generate a serial number within a date value. I cannot use identity field
for this. I have provided below the table creation script and sample data.
create table Gen_Serial
(LoadDate SmallDateTime,
SerialNum int,
Data int) insert into Gen_Serial(LoadDate, SerialNum, Data)
values(’10-Jun-2007′, 1, 10) insert into Gen_Serial(LoadDate, SerialNum, Data)
values(’10-Jun-2007′, 2, 15) insert into Gen_Serial(LoadDate, SerialNum, Data)
values(’10-Jun-2007′, 3, 12) insert into Gen_Serial(LoadDate, SerialNum, Data)
values(’10-Jun-2007′, 4, 14) insert into Gen_Serial(LoadDate, SerialNum, Data)
values(’15-Jun-2007′, NULL, 24) insert into Gen_Serial(LoadDate, SerialNum, Data)
values(’15-Jun-2007′, NULL, 35) insert into Gen_Serial(LoadDate, SerialNum, Data)
values(’15-Jun-2007′, NULL, 24) insert into Gen_Serial(LoadDate, SerialNum, Data)
values(’15-Jun-2007′, NULL, 44) insert into Gen_Serial(LoadDate, SerialNum, Data)
values(’15-Jun-2007′, NULL, 12) insert into Gen_Serial(LoadDate, SerialNum, Data)
values(’15-Jun-2007′, NULL, 23)
go select * from Gen_Serial
go In the above output, I want to store values from 1 to 6 in SerialNum field for LoadDate equal to ”15-Jun-2007′.
Please help me to write SQL statement for this requirement, thank you. Regards,
Devendran R

http://www.databasejournal.com/scripts/article.php/2224051 Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thank You Satya. Regards,
Devendran R

1 If you want to show them in reports, group the report by date and use running total with count and reset to each group 2
declare @i int
select @i=0 Update #Gen_Serial
set [email protected],@[email protected]+1
where serialNum is null Select * from #Gen_Serial
Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Madhivanan 1 If you want to show them in reports, group the report by date and use running total with count and reset to each group 2 declare @i int select @i=0 Update #Gen_Serial set [email protected],@[email protected]+1 where serialNum is null Select * from #Gen_Serial Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> You know that you’re relying on undocumented behaviour? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] You have no control over the order in which the rows are updated. If that is of any importance, this technique is risky. – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

I agree [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Thanks a lot Madhi and FrankKalis. Regards,
Deva

]]>