Problem

In manufacturing, sometimes you need to do strange things, like cause certain parts to be interleaved when they run down the line. I recently ran across this problem at work and wanted to share my solution to this problem.

In this example there is a single work order with a quantity of five with the color of red. There is a second work order quantity of 5 color of white. The first part of the problem is breaking out the two work order rows to ten rows because of the quantity. The second problem is interleaving the rows, so you get one red, then one white.

An example

I created a table, called OddEvenOrder, and inserted six rows into it. The first four rows are work orders with only a quantity of one so nothing needs to be done there, those orders are already matching up correctly based on the size.

CREATE TABLE dbo.OddEvenOrder(
OddEvenOrderid INT NOT NULL IDENTITY(1,1),
ScheduleNumber INT NOT NULL,
WorkOrderNumber INT NOT NULL,
Quantity INT NOT NULL,
Color VARCHAR(20) NOT NULL,
Size VARCHAR(20) NOT NULL,
SortKey VARCHAR(50) NOT NULL,
UpdatedSortKey VARCHAR(50) NULL,
CONSTRAINT [PK_OddEvenOrder] PRIMARY KEY CLUSTERED 
(
[OddEvenOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT dbo.OddEvenOrder
    (ScheduleNumber
   , WorkOrderNumber
   , Quantity
   , Color
   , Size
   , SortKey
   , UpdatedSortKey)
VALUES
    (100, 20001, 1, 'Orange', '3x5', 'Line 1 Slot01-01', ''),
    (100, 20002, 1, 'Green', '3x5', 'Line 1 Slot02-02', ''),
    (100, 20003, 1, 'Purple', '5x4', 'Line 1 Slot03-03', ''),
    (100, 20004, 1, 'Yellow', '5x4', 'Line 1 Slot04-04', ''),
    (100, 20005, 5, 'Red', '6x6', 'Line 1 Slot05-09', ''),
    (100, 20006, 5, 'White', '6x6', 'Line 1 Slot10-14', '')
GO

Here is what the data looks like:

Breaking out two rows to ten

To break out the rows I used a tally table. This is a table that contains rows with numbers in them. In my case I created a tally table with rows with the values 1 to 10. This works in my example, but in real life you would need a tally table that had values that reached the max quantity someone could order on a work order.

CREATE TABLE [dbo].[Tally](
[Number] [int]  NOT NULL,
CONSTRAINT [PK_Tally] PRIMARY KEY CLUSTERED 
(
[Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO dbo.Tally
(
Number
)
VALUES
(1  -- Number - int
),(2),(3),(4),(5),(6),(7),(8),(9),(10)

When you join the work order table to the tally table, the join is on the Number column in the tally table between 1 and the quantity of the work order.

DECLARE @startingSerialNumber BIGINT = 1000000
SELECT OddEvenOrderid,
ScheduleNumber,
WorkOrderNumber,
Quantity,
Color,
Size,
SortKey,
UpdatedSortKey,
@startingSerialNumber - 1 + ROW_NUMBER() OVER(ORDER BY oe.WorkOrderNumber) AS 'SerialNumber'
FROM dbo.OddEvenOrder oe
JOIN dbo.Tally t ON t.Number BETWEEN 1 AND oe.Quantity

So that give us this result, with 10 rows for work orders 20005 and 20006, with 1 for each of the others, matching the quantity in each work order:

Figuring out how to get odd and even rows

Once we have the work orders broken out, I needed to figure out how I was going to set the first work order to odd numbers and the second work order to even numbers. This is what I realized:  to get an odd number if the numbers you have are 1,2,3,4,5 you need to add 0,1,2,3,4 to them.

1,2,3,4,5

+

0,1,2,3,4

=

1,3,5,7,9

In similar fashion to get even numbers from 1,2,3,4,5 you need to add 1,2,3,4,5 to them.

1,2,3,4,5

+

1,2,3,4,5

=

2,4,6,8,10

Grouping work orders together

The next thing I needed to figure out is how to group the work orders together that I want to interleave. In my example they both have the same size, 6x6. I used that, and I updated the UpdatedSortKey so both work orders have the same SortKey as the first work order. This is the code I used.

UPDATE oe SET oe.UpdatedSortKey = t.updatedSortKey
FROM dbo.OddEvenOrder oe
JOIN (SELECT size, MIN(SortKey) AS 'updatedSortKey' FROM dbo.OddEvenOrder WHERE ScheduleNumber=100 AND Quantity > 1
GROUP BY size) t ON oe.size = t.Size
WHERE oe.ScheduleNumber = 100 AND oe.Quantity > 1

This sets the UpdatedSortKey value to "Line 1 Slot05-09" for both rows with the quantity of 5 and the size of 6x6.

Pulling It All Together

After this update is applied I am now ready to do my addition to set the UpdatedSortKey to have odd and even number so they can sequence red and white together. Since I am only changing work orders that have a quantity > 1, all work orders with a quantity of 1 just get 00 added to them. For the odd numbers I use a Row_number() function and then add to it the same Row_Number() function -1. I partition by the UpdatedSortKey and color, so any time the UpdateSortKey and color change the value starts at 1 again. In my real life example there were many of these which is why the UpdatedSortKey is in the partition.

DECLARE @startingSerialNumber BIGINT = 1000000
SELECT OddEvenOrderid,
ScheduleNumber,
WorkOrderNumber,
Quantity,
Color,
Size,
SortKey,
CASE WHEN oe.Quantity = 1 THEN SortKey+'00' ELSE updatedsortkey+
RIGHT('0'+ CAST(ROW_NUMBER() OVER(PARTITION BY oe.UpdatedSortKey, oe.Color ORDER BY oe.WorkOrderNumber)+
CASE WHEN color = 'Red' THEN ROW_NUMBER() OVER(PARTITION BY oe.UpdatedSortKey ORDER BY oe.WorkOrderNumber)-1 --this is odd
ELSE ROW_NUMBER() OVER(PARTITION BY oe.UpdatedSortKey, oe.Color ORDER BY oe.WorkOrderNumber) END --this is even
AS VARCHAR(3)),2) END as'UpdatedSortKey',
@startingSerialNumber - 1 + ROW_NUMBER() OVER(ORDER BY oe.WorkOrderNumber) AS 'SerialNumber'
FROM dbo.OddEvenOrder oe
JOIN dbo.Tally t ON t.Number BETWEEN 1 AND oe.Quantity

So now we have this:

Finally if we order by the calculated UpdatedSortKey we get the new sequencing we set out to achieve.

DECLARE @startingSerialNumber BIGINT = 1000000
SELECT OddEvenOrderid,
ScheduleNumber,
WorkOrderNumber,
Quantity,
Color,
Size,
SortKey,
CASE WHEN oe.Quantity = 1 THEN SortKey+'00' ELSE updatedsortkey+
RIGHT('0'+ CAST(ROW_NUMBER() OVER(PARTITION BY oe.UpdatedSortKey, oe.Color ORDER BY oe.WorkOrderNumber)+
CASE WHEN color = 'Red' THEN ROW_NUMBER() OVER(PARTITION BY oe.UpdatedSortKey ORDER BY oe.WorkOrderNumber)-1 --this is odd
ELSE ROW_NUMBER() OVER(PARTITION BY oe.UpdatedSortKey, oe.Color ORDER BY oe.WorkOrderNumber) END --this is even
AS VARCHAR(3)),2) END as'CalcUpdatedSortKey',
@startingSerialNumber - 1 + ROW_NUMBER() OVER(ORDER BY oe.WorkOrderNumber) AS 'SerialNumber'
FROM dbo.OddEvenOrder oe
JOIN dbo.Tally t ON t.Number BETWEEN 1 AND oe.Quantity
ORDER BY CalcUpdatedSortKey

Here are the results:

Conclusion

I hope you enjoyed my solution to this problem. Perhaps it will help you if you need to change the sequencing through setting rows to odd and even numbers.