Generate a sequence, and produce the cyclic elements using the division and modulo operators.
Some sequence-generation problems require values that go through cycles. Suppose that you’re manufacturing items such as pharmaceutical products or automobile parts, and you must be able to track them by lot number if manufacturing problems are discovered later that require items sold within a particular lot to be recalled. Suppose also that you pack and distribute items 12 units to a box and 6 boxes to a case. In this situation, item identifiers are three-part values: the unit number (with a value from 1 to 12), the box number (with a value from 1 to 6), and a lot number (with a value from 1 to whatever the highest case number happens to be currently).
This item-tracking problem appears to require that you maintain three counters, so you might think about generating the next identifier value using an algorithm like this:
retrieve most recently used case, box, and unit numbers unit = unit + 1 # increment unit number if (unit > 12) # need to start a new box? { unit = 1 # go to first unit of next box box = box + 1 } if (box > 6) # need to start a new case? { box = 1 # go to first box of next case case = case + 1 } store new case, box, and unit numbers
You could indeed implement an algorithm that way. However, it’s
also possible simply to assign each item a sequence number identifier
and derive the corresponding case, box, and unit numbers from it. The
identifier can come from an AUTO_INCREMENT
column or a single-row
sequence generator. The formulas for determining the case, box, and
unit numbers for any item from its sequence number look like
this:
unit_num = ((seq - 1) % 12) + 1 box_num = (int ((seq - 1) / 12) % 6) + 1 case_num = int ((seq - 1)/(6 * 12)) + 1
The following table illustrates the relationship between some sample sequence numbers and the corresponding case, box, and unit numbers:
seq | case | box | unit |
---|---|---|---|
1 | 1 | 1 | 1 |
12 | 1 | 1 | 12 |
13 | 1 | 2 | 1 |
72 | 1 | 6 | 12 |
73 | 2 | 1 | 1 |
144 | 2 | 6 | 12 |
3.133.82.244