I need help to write a stored procedure, or
pseudo code, that looks into a table named numbers_ranges
and retrieves the next unused value.

The table currently has the following data:

Columns are: id, start_range (22), end_range (22), status (1), date_created (datetime), date_updated (datetime)

1 8057777777450780000000 8057777777450780000010 N 2007-11-29 16:28:46.793 2007-11-29 16:28:46.793
2 8057777777450780000011 8057777777450780000021 N 2007-11-29 16:28:56.497 2007-11-29 16:28:56.497
3 8057777777450780000022 8057777777450780000032 N 2007-11-29 16:29:04.637 2007-11-29 16:29:04.637
4 8057777777450780000033 8057777777450780000043 N 2007-11-29 16:29:13.150 2007-11-29 16:29:13.150
5 8057777777450780000044 8057777777450780000054 N 2007-11-29 16:29:28.340 2007-11-29 16:29:28.340

How is determined what range to use?

The next number value will be retrieved from the range
with the lowest value and with a status of 'N'

When a number has been retrieved then the status of 'N'
needs to change to 'Y' which indicates that now that range
is in use.

When a range of numbers has been used completely, then
the status for that row need to be changed to 'X'

There are 3 possible status:

N = not used
Y = in use
X = already used

Every time that a number is retrieve from the current range,
or when the status of a range changes, the date_updated column
needs to be updated with the current date.

I understand what needs to be done, but I am confused in where to begin.
I am aware that to ask for this kind of help is not nice but if someone
could at least provide me with some guidance I will fell less stressed.

Thank you much in advance.

p.s.

please let me know if the explanation above was not clear.

Hey .. which columns do you want to retrieve ?

I got a better idea of what needs to happen with this stored procedure...please let me explain myself again...thank you.

Need to retrieve the next available number. Receive a file with a list of tracking numbers.

Each number is 22 characters long. Example: 3027777777450580000001

Position 14 and 15 = service level code in this case: 58

The service level will always be 2 characters position 14 and 15 within the number.

I will insert the contents of this file into a sql server table name estafeta_numbers

There are three types of status:

A = active
N = not active
X = already used

When the file is inserted the first time, all the numbers will have a status of 'N' and the last_modified_date will be = create_date

When a new tracking number is retrieved its status needs to be updated to 'A' and the one that was used before its status will change to 'X'

The stored procedure that i need help with, will receive input parameter: Service Level, and return output parameter: Tracking Number


Below I paste sample table and data.

Thank you much,


table structure:

CREATE TABLE t_estafeta_range
(id int IDENTITY(1,1),
estafeta_tracking_number varchar(22) not null,
created_date datetime not null,
last_tracking_number_used varchar(22) null,
status varchar(1) not null,
last_modified_date datetime not null)


Dummy Data:

INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000001', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450600000002', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450680000003', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450880000004', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000005', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000006', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000007', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450770000008', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450550000009', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000010', getdate(), NULL, 'N', getdate())