November 29, 2007 at 4:19 pm
If possible 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.
I also have attached a text file with some test data.
November 29, 2007 at 4:30 pm
-- By first ID
select *
from number_ranges
where id = (select min(id)
from number_ranges
where status = 'N')
-- By Lowest Start Range
select *
from number_ranges
where start_Range = (select min(start_Range)
from number_ranges
where status = 'N')
If you want to get the ID, and update it at the same time
declare @id int,
@Start_Range varchar(22),
@End_Range varchar(22)
Update number_Ranges
set status = 'Y'
, @ID = id
, @Start_Range = start_range
, @End_Range = end_range
where id = (select min(id)
from number_ranges
where status = 'N')
Select @ID, @Start_Range, @EndRange
November 29, 2007 at 5:12 pm
thank you for your post.
i forgot to mention one thing.
the value for the current_number_in_use will be stored in a table named current_number_value column current_number_in_use
this table has a 0 right now.
the first thing in the procedure i need to perform is to go to this table, get the current number value
and with this number check the table with the number ranges.
using this number (0) then i need to select the lowest start range number, with status of N and that the number is between the ranges, does this makes sens so far?
but then the next time that the stored procedure is called, the number from current_number_value
will be 8057777777450780000000
now i need to select from the row with a Y if the number is between start and end and not 8057777777450780000000 ideally the number to be retrieve will be 8057777777450780000001, right?
i apologize if this is confusing i am doing my best to be clear.
thank you,
November 29, 2007 at 9:55 pm
Sure,
But your logic makes no sense, Do you need to return the Number RangeID, or just the next number??
create Proc ReturnNumber @CurrentNumber bigint output
as
-- Get the current number
select @CurrentNumber = current_number_in_use
from current_number_value
-- First check if current_number_in_use is in a range, if so return next number
if exists (select * from number_Ranges where @CurrentNumber between Start_Range and End_Range -1 and status = 'Y')
begin
-- update current_number_value to hold the next number and return the next number
update current_number_value
set current_number_in_use = current_number_in_use + 1
, @CurrentNumber + 1
end
else
begin
-- not already within an existing range then find the first range where status = 'N'
-- Get the first number
select @CurrentNumber = min(start_Range)
from number_Range
where status = 'N'
-- Activate (If you will) the range,
update number_range
set status = 'Y'
where start_range = @CurrentNumber
-- update current_number_value to hold this number
update current_number_value
set current_number_in_use = @CurrentNumber
end
November 30, 2007 at 10:51 am
I had to make some changes to the numbers ranges table.
Now the last number used is no longer stored in a different table.
II just added a column that will stored in the same table.
I am pasting the create table, insert into, and the stored procedure as it is now so you can see the same I see.
CREATE TABLE [dbo].[hc_estafeta_tracking_numbers_ranges](
[range_id] [int] IDENTITY(1,1) NOT NULL,
[range_start_val] [numeric](22, 0) NOT NULL,
[range_end_val] [numeric](22, 0) NOT NULL,
[last_number_used] [numeric](22, 0) NULL,
[range_status] [varchar](1) COLLATE Latin1_General_BIN NOT NULL,
[range_create_date] [datetime] NOT NULL,
[range_updated_date] [datetime] NOT NULL
INSERT INTO hc_estafeta_tracking_numbers_ranges([range_start_val],[range_end_val],[last_number_used],[range_status],[range_create_date],[range_updated_date])VALUES(8057777777450780000000,8057777777450780000010,0,'N',getdate(),getdate())
INSERT INTO hc_estafeta_tracking_numbers_ranges([range_start_val],[range_end_val],[last_number_used],[range_status],[range_create_date],[range_updated_date])VALUES(8057777777450780000011,8057777777450780000021,0,'N',getdate(),getdate())
INSERT INTO hc_estafeta_tracking_numbers_ranges([range_start_val],[range_end_val],[last_number_used],[range_status],[range_create_date],[range_updated_date])VALUES(8057777777450780000022,8057777777450780000032,0,'N',getdate(),getdate())
INSERT INTO hc_estafeta_tracking_numbers_ranges([range_start_val],[range_end_val],[last_number_used],[range_status],[range_create_date],[range_updated_date])VALUES(8057777777450780000033,8057777777450780000043,0,'N',getdate(),getdate())
INSERT INTO hc_estafeta_tracking_numbers_ranges([range_start_val],[range_end_val],[last_number_used],[range_status],[range_create_date],[range_updated_date])VALUES(8057777777450780000044,8057777777450780000054,0,'N',getdate(),getdate())
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[hc_get_next_estafeta_traking_number]
AS
BEGIN TRANSACTION
------
UPDATE [hc_estafeta_tracking_numbers_ranges]
SET [last_number_used] = case [last_number_used]
-- if the value = 0 then select the lowest start value
when 0 then
(SELECT MIN([range_start_val])
FROM [hc_estafeta_tracking_numbers_ranges]
WHERE [range_status] = 'N')
else
-- if the current range is good (haven't reached the end yet) then just add 1 to the current value
case
when EXISTS (SELECT [range_id]
FROM [hc_estafeta_tracking_numbers_ranges]
WHERE [last_number_used] + 1 BETWEEN [range_start_val] and [range_end_val]) then [last_number_used] + 1
-- else find the start value of the next range
else (SELECT MIN([range_start_val])
FROM [hc_estafeta_tracking_numbers_ranges]
WHERE [last_number_used] + 1 < [range_start_val])
end
end
----------------------
UPDATE [hc_estafeta_tracking_numbers_ranges]
SET [range_status] = case
when [last_number_used] < [range_end_val] then 'N'
when [last_number_used] between [range_start_val] and [range_end_val] then 'Y'
when [last_number_used] > [range_end_val] then 'X'
end,
[range_updated_date] = case
when [last_number_used] between [range_start_val] and [range_end_val] OR [last_number_used] = [range_end_val] then getdate()
else [range_updated_date]
end
FROM [hc_estafeta_tracking_numbers_ranges]
COMMIT TRANSACTION
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply