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.