Get Next Available Number

[robertfah]

We are building an Issue program that allows users to enter Issues. They will use this program to enter trouble issues with our products (when customers call). One requirement is that upon opening a NEW issue, the Issue Number be displayed so the user can tell the customer their issue number without having to save it right away (for various reasons I won't get into). We don't need to reuse issue numbers but we have to "reserve" them when a user opens an issue (because if they save the issue, then that will be a the issueNumber the customer will use for future reference).

We created an Issue Table (IssueID being the IssueNumber and it's not auto-incrementing) and a IssueNumberQueue table. We thought we could use the IssueNumberQueue to track what IssueNumbers are being "reserved" and to know what is the next issue number.

So we need a way to generate the next available issue number for the program so that when the user selects "New Issue", they get the first available issue number that's not reserved or already used.

Here's what I started with but it could be all wrong:

	DECLARE @MaxIssueID int
		
	SELECT @MaxIssueID = MAX(IssueID) + 1
	FROM Issues
	
	SELECT NextIssueNumber
	FROM IssueNumberQueue
	WHERE NextIssueNumber = @MaxIssueID
	
	--IssueNumber not in use.
	IF(@@ROWCOUNT = 0)
		BEGIN
			INSERT INTO IssueNumberQueue
			VALUES (@MaxIssueID)
		END
	ELSE --IssueNumber in use, get another one.
		BEGIN
			--Some form of loop that will add 1 to the @MaxIssueID variable and search again to see if it's in use.
		END

	
	INSERT INTO IssueNumberQueue
	VALUES (@MaxIssueID)
	
	SELECT @MaxIssueID AS NextIssueNumber

Any ideas?

One thing is we do not have to use EVERY issue number....so let's say I have 5 users and they all open a new issue....issuenumbers 1, 2, 3, 4, & 5 would be "reserved".....if issue# 1, 3, & 5 get saved but issue#'s 2 & 4 don't, then we don't have to "re-use" #'s 2 &4, they would just be ignored and #6 would be the next available number.

 

[ousoonerjoe]

This is a rough sketch, but without a next_seq call like Oracle has, this is a relative easy and dirty way to get there.

Create a table with your Id Number column. (yes, 1 row, 1 column, but allows for you to be able to create multiple sequence numbers in a single place).

Then every time you need a new number call something to the effect of:

CREATE PROCEDURE cst_NextIssueNumber
AS 

BEGIN TRANSACTION;
	UPDATE SequenceTable
	SET IssueId = ((SELECT MAX(IssueId) FROM SequenceTable) + 1);

	SELECT IssueId FROM SequenceTable;
COMMIT TRANSACTION;

It's ugly, but since you can skip numbers, the IssueId will always increment and never repeat. Let the unused numbers just be skipped.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[gmmastros]

We do something similar here. With our system, we create a row in the issue table immediately so that we have the issue number (and yes, it is an auto incrementing column). We do not allow our customer support staff to delete any issues, but a manager can. Regardless, every issue call is logged, and can be immediately resolved if it is handled during the initial call.

I would suggest that you take a close look at your current system and determine under what circumstances issues are NOT being saved, and then determine if there is a down-side to saving it in the first place.

Even if you decide to allow for "Not saving" you could do this...

Add a new row to the issue table at the beginning of the call. Then have the system save additional information that your support people capture when the "save" button is clicked, or delete the row when the "I don't want to save" button is clicked.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

DISCLAIMER: This wasn't my idea, I inherited it.

This is kinda dirty, but we had a table that held a bunch of "IDENTITY" values.
The table only had 1 row with about a dozen columns.
Each column was the next identity to use (for the particular table in question).
The tables were set up with a int UNIQUE primary key field in lieu of a int identity(1,1) Primary key.

You would "Take a number" like at the DMV.
The next person would "Take a number"

You would use that number if you actually saved your data, or it got "skipped/discarded" if you walked away early.

Ideally you would follow GMM's suggestion and store some basic data about the transaction (pontentially to see who is dropping sales or stealth fixing HD issues).

Lodlaiden



I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[robertfah]

We do something similar here. With our system, we create a row in the issue table immediately so that we have the issue number (and yes, it is an auto incrementing column). We do not allow our customer support staff to delete any issues, but a manager can. Regardless, every issue call is logged, and can be immediately resolved if it is handled during the initial call.

I would suggest that you take a close look at your current system and determine under what circumstances issues are NOT being saved, and then determine if there is a down-side to saving it in the first place.

Even if you decide to allow for "Not saving" you could do this...

Add a new row to the issue table at the beginning of the call. Then have the system save additional information that your support people capture when the "save" button is clicked, or delete the row when the "I don't want to save" button is clicked.

I like this idea....but what about table relationships that are setup and columns that don't allow nulls? I guess all your columns would have to allow nulls and then do an INSERT as suggested and if they save the data, perform an update on that ID...right?

DISCLAIMER: This wasn't my idea, I inherited it.

This is kinda dirty, but we had a table that held a bunch of "IDENTITY" values.
The table only had 1 row with about a dozen columns.
Each column was the next identity to use (for the particular table in question).
The tables were set up with a int UNIQUE primary key field in lieu of a int identity(1,1) Primary key.

You would "Take a number" like at the DMV.
The next person would "Take a number"

You would use that number if you actually saved your data, or it got "skipped/discarded" if you walked away early.

Ideally you would follow GMM's suggestion and store some basic data about the transaction (pontentially to see who is dropping sales or stealth fixing HD issues).

Lodlaiden

Scary, but my boss who's not a SQL guy or programmer by any means, thought of this solution as well.

 

[Olaf Doschke]

What you describe perfectly fits to integer identity fields and I think you know that.

Just for the reason you don't want to save a new issue right away you are programming your own mechanism of autoincrementing values. Why?

You could have a dedicated table just for creation of the issue numbers, the table needs nothing more than an issueno integer identity field and some dummy field to be able to write an INSERT statement. You simply insert a new record there and retrieve your next issue number to display and use as pk value for the real issues table.

That's the easiest and most reliable integer identity generator you can have without using it on the issues table itself. You can even truncate that table and the counter will still continue from it's last position.

Bye, Olaf.

 

[robertfah]

What you describe perfectly fits to integer identity fields and I think you know that.

Just for the reason you don't want to save a new issue right away you are programming your own mechanism of autoincrementing values. Why?

You could have a dedicated table just for creation of the issue numbers, the table needs nothing more than an issueno integer identity field and some dummy field to be able to write an INSERT statement. You simply insert a new record there and retrieve your next issue number to display and use as pk value for the real issues table.

That's the easiest and most reliable integer identity generator you can have without using it on the issues table itself. You can even truncate that table and the counter will still continue from it's last position.

Bye, Olaf.

Another great idea...and yes, very simple. To be honest, we have a 3rd party software that we use for our "issue tracking" and we are now creating our own. My job has been to reverse engineer the software and DB to have our software act the same. This is the first phase which we tend to get the same look and feel as our existing software and then switch over. Once our users are comfortable and the bugs are worked out, we will implemement new features and change what we don't like. Sometimes I get caught up in the "weeds and seeds" of things and don't step back to look at the easier solutions.

Originally, my boss wanted to be able to recycle unsaved issue numbers IF there wasn't a greater issue number saved....meaning if my five users open five new issues (issue#'s 1, 2, 3, 4, & 5) and only #'s 1 & 3 get saved, well my boss wanted to see #4 get reused because it's the next in line (so to speak). That's where I was trying to go with the original stored procedure but was having difficulties.

 

[ousoonerjoe]

As we all have experienced, he doesn't want to save them now, but eventually will want a report so he can quantify the call volume verses the tickets created. George is on the right track. Create the record with default values. Add a BIT field that indicates if the record classifies as a "Saved" record. Any information the users give is truly saved, but only the ones flagged are used for reporting, working, etc.

So in 6 months when he asks for that report, you can hand it to him instead of reworking the program to support the request we all know is inevitable.... Managers do love their reports.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[Olaf Doschke]

If the reporting of number of calls vs tickets is the reasoning, suich a flag as poposed by ousoonerjoe is the way I would handle it, too. The seperate identity table solution is good, if an insert the issues table would trigger something (eg literally an insert trigger), which should not be triggered, if the issue isn't stored, eg creating additionl records you don't want at all, if the call turns out to resolve the issue right away anyway.

Bye, Olaf.

 

[robertfah]

I can't see us reporting on the number of calls vs. the number of tickets opened. We do have a high call volume but our customers pay for their support (yearly) and are not concerned with # of calls vs. tickets that have been opened from those calls.

I like GMM's and Olaf's ideas but with GMM's idea, inserting a "blank" record and then deleting it if not saved, wouldn't I have to break the table relationships that are setup (referential integrity)? I know I can "Allow Nulls" on a table but the relationship is what I'm not too sure of.

I've got another question that I'll open a new post for...after hearing these ideas, I'd like to get your opinions on another "issue" we are trying to tackle.

 

[gmmastros]

If you add a row to your "parent" table, and put NULL in to the columns with the foreign key relationships, you will not have a problem. Foreign key relationships allow null. If the value is not null, it must exist in the other table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

Even if, like gmmastros describes, a foreign key constraint would not support NULL or you set foreign key fields to not be NULLable, you could define default foreign key values plus constraints on the foreign/parent tables for those default records to not be deletable.

Nullable foreign key fields are simpler to handle, though.

On the other side, if you don't need and want those issue records for reference of used vs unused issue tickets, you could use my idea.

Bye, Olaf.