[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

SQL Server Number Incrementation Function

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
Cragly
Asked:
Cragly
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
CraglyAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
CraglyAuthor Commented:
Many thanks for your help Angel what a star
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now