Solved

# SQL Server Number Incrementation Function

Posted on 2007-03-24
244 Views
Hi all,

I have a database table in SQL Server 2005 where I have a field called JobNumber and is set to type decimal(18,3) so that I can create job numbers in the format of 6000.000.

What I want to do is everytime I add a new job to the system using a stored proc is get the last job number and add either 5 or 7 to it. If its a sub job then I would just like to add .001 to the number.

Example:

Job number starts at 6000.000 and I add a job I would like the job number to randomly select the number 5 or 7 and then add it to the current job number and save this as the new job number so we would have 6005.000 or 6007.000.

If I identify the job as a sub job then I just want to increment this by adding .001 so it would be 6000.001.

Any help on how to achive this within SQL Server would be very helpful. I was thinking of using some form of function to do this but I am sure there is some knowledgable SQL Server expert out there who can tell me if this is the way to go or not.

Many thanks

Cragly

0
Question by:Cragly
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 143

Expert Comment

ID: 18785805
>I have a field called JobNumber and is set to type decimal(18,3) so that I can create job numbers in the format of 6000.000.

I would rather suggest to make that 2 fields, 1 for jobnumber, and a second field for job sub step for example, both int. also, the sub jobs should eventually be defined in a second table, but without know exactly what this job stuff is all above, it's difficult/impossible to give a definitive answer.

I am very curious to know why you want not a purely incremental (+1) sequence, but +5/+7...

0

Author Comment

ID: 18785874
Many thanks for getting back to me so quickly its much appreciated.

The reason behind the +5/+7 is that my client uses the job number as an invoice number and he never uses just +1. He is very creative with his invoices so as not to look like he is doing all his work with just one client.

I agree with what you say regarding splitting up the numbers into seperate fields and I will have a Jobs table with number and subNumber.

What I now need to do is create a function that gets the highest job number and then randomly selects 5 or 7 and does the addition and returns it back to the calling sproc to use as the new job number.

All I need to do now is find out how to select 5 or 7 randomly within the function.

Any ideas??

Cheers

Cragly
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 18785910
to get a random number, you can inspire yourself here:
http://www.drdev.net/article07.htm

to get the highest existing number, use
declare @r int
SELECT @r = FLOOR(MAX(jobnumber)) from yourtable

and add the random value as needed.
you can put that of course into a function.
0

Author Comment

ID: 18785929
Many thanks for your help Angel what a star
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

SQL 2005 - Memory Table Column Names 11 87
user defined date datatype in SQL Server- can it be overdone.. 6 42
SQL Server Insert where not exists 24 57
What is this datetime? 1 33
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Simple Linear Regression
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
###### Suggested Courses
Course of the Month8 days, 3 hours left to enroll