Solved

SQL Server Number Incrementation Function

Posted on 2007-03-24
4
204 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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 video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
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.

743 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

14 Experts available now in Live!

Get 1:1 Help Now