December 6, 2007 at 2:02 pm
Hello all,
I hope I ask this question correctly, and with the information necessary to help adequate help.
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 ('3027777777450580000002', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000003', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000004', 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 ('3027777777450580000008', getdate(), NULL, 'N', getdate())
INSERT t_estafeta_range (estafeta_tracking_number, created_date, last_tracking_number_used, status, last_modified_date) VALUES ('3027777777450580000009', 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())
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply