Solved

SQL Server Number Incrementation Function

Posted on 2007-03-24
4
216 Views
Last Modified: 2010-03-19
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
Comment
Question by:Cragly
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
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

by:Cragly
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 142

Accepted Solution

by:
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

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select - Finding chars in a column 2 56
Record open by another user 6 50
how many extra RAM for SQL server is needed 22 34
INSERT DATE FROM STRING COLUMN 18 46
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now