Sequential Numbering / how to do with a query...

[Guest]

Good day folks,

I have a table called "Serial_Numbers" wich contains the following fields :

Week_number - Data type : TEXT with Default Value ->DatePart("ww",Date(),1)
Year_number - Data type : TEXT with Default Value -> Format(Date(),"yy")
Seq_number - Data type : TEXT

By knowing that
Week_number = 43
Year_number = 04
Seq_number = 001;
how can I tell the field "Seq_number" to increment itself FOR "Week_number"
= 43 and then reset itself when "Week_number" = 44
Here's a sample of what I should see :

Week_number Year_number Seq_number
43 04 001
43 04 002
43 04 003
44 04 001

What is the best way to do it? from a query with SQL or with VB coding?

Thanks for your help

 

[Guest]

how can I tell the field "Seq_number" to increment itself FOR "Week_number"
= 43 and then reset itself when "Week_number" = 44

If you only want to do this once, as opposed to have a key that's being
updated all the time, I'd suggest using straight SQL. If you are going to be
generating these all the time, then you might want to consider putting it
into a stored proc.

Anyway...

Here's a sample of what I should see :

Week_number Year_number Seq_number
43 04 001
43 04 002
43 04 003
44 04 001

So basically if (week + year) = oldWeekAndYear, increment seq number,
otherwise reset it. If that's the right description, then...

Select the data into a new tem table, ordered by year_number then week
number. That builds the first two columns of your list. Setup a variable
called "oldWeekAndYear" (or somesuch), and give it the empty string. Also
create a variable for the sequence, and set it to 1.

Now make a cursor on that table, and for each FETCH, put the two strings
together (if they are really ints, use CONVERT(varchar(2), year_number) for
instance) to create something like "0443". Compare that with oldWeekAndYear,
if it is different, reset sequence to 1, otherwise add 1 to it. Write that
number into the cursor (UPDATE ... WHERE CURRENT OF cursorname). That should
do it.

There might be a way to do this with groupby and count, but I can't figure
it out.

Maury

 

[Guest]

Thanks Maury for your fast reply! :0)

Yes, this "condition" will need to run each time a user will create a new
record, in
other words it will need to validate both Week and Seq numbers before
creating the record.

For the most part, I understand your logic but there's one point i'm
missing.. the Cursor. Can you give me an example of how to write if down in
my code?

thanks again

 

[Guest]

For the most part, I understand your logic but there's one point i'm
missing.. the Cursor. Can you give me an example of how to write if down in
my code?

Sure, but sadly cursors in T-SQL are a bit annoying (they're much better in
Oracle for instance).

DECLARE CURSOR myCursor FOR
SELECT week_number, year_number FROM whateverYouCalledIt ORDER BY xxx

OPEN CURSOR myCursor
FETCH NEXT myCursor INTO @week, @year

WHILE @@FETCH_STATUS = 0
BEGIN
do whatever you want here...


UPDATE whateverYouCalledIt SET whatYouWant WHERE CURRENT OF myCursor

FETCH NEXT myCursor INTO @week, @year
END

CLOSE myCursor
DEALLOCATE myCursor

It's just a basic loop, but it is pretty annoying the way it demands all the
little declares and opens and such.