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]

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.OddEvenOrder
	(
	    ScheduleNumber,
	    WorkOrderNumber,
	    Quantity,
	    Color,
	    Size,
	    SortKey,
	    UpdatedSortKey
	)
	VALUES
	(   100,  -- ScheduleNumber - int
	    20001,  -- WorkOrderNumber - int
	    1,  -- Quantity - int
	    'Orange', -- Color - varchar(20)
	    '3x5', -- Size - varchar(20)
	    'Line1 Slot01-01', -- SortKey - varchar(50)
	    ''  -- UpdatedSortKey - varchar(50)
	    ),
		(100,20002,1,'Green','3x5','Line1 Slot02-02',''),
		(100,20003,1,'Purple','5x4','Line1 Slot03-03',''),
		(100,20004,1,'Yellow','5x4','Line1 Slot04-04',''),
		(100,20005,5,'Red','6x6','Line1 Slot05-09',''),
		(100,20006,5,'White','6x6','Line1 Slot10-14','')
		
		SELECT * FROM dbo.OddEvenOrder

		INSERT INTO dbo.Tally
		(
			Number
		)
		VALUES
		(1  -- Number - int
			),(2),(3),(4),(5),(6),(7),(8),(9),(10)

		--break out individual units based on work order quantity using tally table
		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

		 --Finally Interleave red and white work orders together
		 --First we have to create a key to link the two work orders together
		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 select shows you the final interleave of red and white work orders 
		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
		 ORDER BY 8