Solved

SQL Server Number Incrementation Function

Posted on 2007-03-24
4
249 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
[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
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 143

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 143

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

724 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