The check constraint will fire an error if the number of carton eggs is less than 1, or more than 12, so the operation will automatically roll-back.
And I decided to not have a Baskets table, because it is equally easy to call a stored procedure directly with the number of eggs in the basket as a parameter, as it is to store the basket in a table.

I can't seem to post my solution as it says it's too long!

According to the field validator, this text box should allow 8,000 chars and mine is ~7,000 but it still won't let me post it :o(

CREATE TABLE [Location] (
  [LocationId] INT NOT NULL IDENTITY (1, 1),
  [Location] VARCHAR(255) NOT NULL,
  CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED ([LocationId])
)
GO

INSERT INTO [Location] ([Location])
SELECT 'Joe''s Barn' UNION
SELECT 'Celko''s Yard'
GO

CREATE TABLE [Basket] (
  [BasketId] INT NOT NULL IDENTITY (1, 1),
  [LocationId] INT NOT NULL,
  [CollectedWhen] DATETIME NOT NULL,
  CONSTRAINT [PK_Basket] PRIMARY KEY CLUSTERED ([BasketId]),
  CONSTRAINT [FK_Basket_Location] FOREIGN KEY ([LocationId]) REFERENCES [Location] ([LocationId]),
  CONSTRAINT [CK_Basket_CollectedWhen] CHECK ([CollectedWhen] <= GETDATE())
)
GO

CREATE TABLE [Egg] (
  [EggId] INT NOT NULL IDENTITY (1, 1),
  [BasketId] INT NOT NULL,
  [BasketNum] TINYINT NOT NULL,
  [BasketPrevNum] TINYINT NULL,
  CONSTRAINT [PK_Egg] PRIMARY KEY CLUSTERED ([EggId])
)
GO

CREATE UNIQUE INDEX [IX_Egg_BasketNum] ON [Egg] ([BasketId], [BasketNum])
GO

ALTER TABLE [Egg] ADD
CONSTRAINT [FK_Egg_Egg] FOREIGN KEY ([BasketId], [BasketPrevNum]) REFERENCES [Egg] ([BasketId], [BasketNum]),
CONSTRAINT [CK_Egg_BasketNum] CHECK ([BasketNum] = ISNULL([BasketPrevNum], 0) + 1)
GO

CREATE TABLE [Carton] (
  [CartonId] INT NOT NULL IDENTITY (1, 1),
  [PackagedWhen] DATETIME NOT NULL,
  CONSTRAINT [PK_Carton] PRIMARY KEY CLUSTERED ([CartonId]),
  CONSTRAINT [CK_Carton_PackagedWhen] CHECK ([PackagedWhen] <= GETDATE())
)
GO

CREATE TABLE [CartonContent] (
  [CartonId] INT NOT NULL,
  [EggId] INT NOT NULL,
  [CartonNum] TINYINT NOT NULL,
  [CartonPrevNum] TINYINT NULL,
  CONSTRAINT [PK_CartonContent] PRIMARY KEY CLUSTERED ([CartonId], [CartonNum]),
  CONSTRAINT [FK_CartonContent_Carton] FOREIGN KEY ([CartonId]) REFERENCES [Carton] ([CartonId]),
  CONSTRAINT [FK_CartonContent_Egg] FOREIGN KEY ([EggId]) REFERENCES [Egg] ([EggId]),
  CONSTRAINT [FK_CartonContent_CartonContent] FOREIGN KEY ([CartonId], [CartonPrevNum]) REFERENCES [CartonContent] ([CartonId], [CartonNum]),
  CONSTRAINT [CK_CartonContent_CartonNum] CHECK ([CartonNum] = ISNULL([CartonPrevNum], 0) + 1 AND [CartonNum] <= 12)
)
GO

CREATE UNIQUE INDEX [IX_CartonContent_EggId] ON [CartonContent] ([EggId])
GO

CREATE PROCEDURE [LogBasketCollection] (@LocationId INT, @NumberOfEggs TINYINT, @CollectedWhen DATETIME = NULL)
AS
BEGIN
SET NOCOUNT ON
DECLARE @BasketId INT
INSERT INTO [Basket] ([LocationId], [CollectedWhen]) VALUES (@LocationId, ISNULL(@CollectedWhen, GETDATE()))
SET @BasketId = SCOPE_IDENTITY()
INSERT INTO [Egg] ([BasketId], [BasketNum], [BasketPrevNum])
SELECT @BasketId, [number], NULLIF([number] - 1, 0)
FROM [master]..[spt_values]
WHERE [type] = 'P'
  AND [number] BETWEEN 1 AND @NumberOfEggs
RETURN @BasketId
END
GO

CREATE PROCEDURE [SplitBaskets]
AS
BEGIN
SET NOCOUNT ON
DECLARE @CartonId INT
WHILE EXISTS (
   SELECT 1
   FROM [Egg] e
    LEFT JOIN [CartonContent] cc ON e.[EggId] = cc.[EggId]
   WHERE cc.[CartonId] IS NULL
  )
BEGIN
  INSERT INTO [Carton] ([PackagedWhen]) VALUES (GETDATE())
  SET @CartonId = SCOPE_IDENTITY()
  ;WITH [Eggs] ([EggId], [CartonNum]) AS (
    SELECT e.[EggId], ROW_NUMBER() OVER (ORDER BY b.[CollectedWhen], e.[BasketId], e.[BasketNum])
    FROM [Egg] e
     JOIN [Basket] b ON e.[BasketId] = b.[BasketId]
     LEFT JOIN [CartonContent] cc ON e.[EggId] = cc.[EggId]
    WHERE cc.[CartonId] IS NULL
   )
  INSERT INTO [CartonContent] ([CartonId], [EggId], [CartonNum], [CartonPrevNum])
  SELECT @CartonId, [EggId], [CartonNum], NULLIF([CartonNum] - 1, 0)
  FROM [Eggs]
  WHERE [CartonNum] <= 12
END
END
GO

CREATE PROCEDURE [SplitCarton](@CartonId INT, @NumberOfEggs TINYINT)
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT 1 FROM [Carton] WHERE [CartonId] = @CartonId) BEGIN
  RAISERROR('The specified carton does not exist!', 16, 1)
  RETURN
END
IF EXISTS (SELECT 1 FROM [CartonContent] WHERE [CartonId] = @CartonId GROUP BY [CartonId] HAVING COUNT(*) <= @NumberOfEggs) BEGIN
  RAISERROR('The number of eggs to be moved must be less than the number of eggs in the carton!', 16, 1)
  RETURN
END
DECLARE @NewCartonId INT
INSERT INTO [Carton] ([PackagedWhen]) VALUES (GETDATE())
SET @NewCartonId = SCOPE_IDENTITY()
;WITH [EggsToMove] ([CartonId], [EggId], [CartonNum], [CartonPrevNum]) AS (
   SELECT
    @NewCartonId,
    [EggId],
    ROW_NUMBER() OVER (ORDER BY cc.[CartonNum]),
    NULLIF(ROW_NUMBER() OVER (ORDER BY cc.[CartonNum]) - 1, 0)
   FROM [CartonContent] cc
    JOIN (
      SELECT [CartonId], COUNT(*) AS [EggsInCarton]
      FROM [CartonContent]
      GROUP BY [CartonId]
     ) eic ON cc.[CartonId] = eic.[CartonId]
   WHERE cc.[CartonId] = @CartonId
    AND cc.[CartonNum] > eic.[EggsInCarton] - @NumberOfEggs
  )
UPDATE cc
SET
  [CartonId] = etm.[CartonId],
  [CartonNum] = etm.[CartonNum],
  [CartonPrevNum] = etm.[CartonPrevNum]
FROM [EggsToMove] etm
  JOIN [CartonContent] cc ON etm.[EggId] = cc.[EggId]
RETURN
END
GO

CREATE PROCEDURE [ConsolidateCartons](@SourceCartonId INT, @DestinationCartonId INT)
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT 1 FROM [Carton] WHERE [CartonId] = @SourceCartonId) BEGIN
  RAISERROR('The specified source carton does not exist!', 16, 1)
  RETURN
END
IF NOT EXISTS (SELECT 1 FROM [Carton] WHERE [CartonId] = @SourceCartonId) BEGIN
  RAISERROR('The specified destination carton does not exist!', 16, 1)
  RETURN
END
IF EXISTS (SELECT 1 FROM [CartonContent] WHERE [CartonId] IN (@SourceCartonId, @DestinationCartonId) HAVING COUNT(*) <= 12) BEGIN
  RAISERROR('The consolidation would result in the source carton being empty!', 16, 1)
  RETURN
END
;WITH [EggsToMove] ([CartonId], [EggId], [CartonNum], [CartonPrevNum]) AS (
   SELECT
    @DestinationCartonId,
    cc.[EggId],
    ROW_NUMBER() OVER (ORDER BY cc.[CartonNum]) + dc.[EggsInDestinationCarton],
    NULLIF(ROW_NUMBER() OVER (ORDER BY cc.[CartonNum]) + dc.[EggsInDestinationCarton] - 1, 0)
   FROM [CartonContent] cc
    JOIN (
      SELECT [CartonId], COUNT(*) AS [EggsInSourceCarton]
      FROM [CartonContent]
      GROUP BY [CartonId]
     ) sc ON cc.[CartonId] = sc.[CartonId]
    CROSS JJOIN (
      SELECT COUNT(*) AS [EggsInDestinationCarton]
      FROM [CartonContent]
      WHERE [CartonId] = @DestinationCartonId
     ) dc
   WHERE cc.[CartonId] = @SourceCartonId
    AND cc.[CartonNum] > sc.[EggsInSourceCarton] - (12 - dc.[EggsInDestinationCarton])
  )
UPDATE cc
SET
  [CartonId] = etm.[CartonId],
  [CartonNum] = etm.[CartonNum],
  [CartonPrevNum] = etm.[CartonPrevNum]
FROM [EggsToMove] etm
  JOIN [CartonContent] cc ON etm.[EggId] = cc.[EggId]
RETURN
END
GO

CREATE VIEW [Cartons]
AS
SELECT
  c.[CartonId],
  c.[PackagedWhen],
  COUNT(cc.[EggId]) AS [NumberOfEggs],
  s.[Source],
  MIN(b.[CollectedWhen]) AS [OldestEgg],
  MAX(b.[CollectedWhen]) AS [NewestEgg]
FROM [Carton] c
  LEFT JOIN [CartonContent] cc
    JOIN [Egg] e ON cc.[EggId] = e.[EggId]
    JOIN [Basket] b ON e.[BasketId] = b.[BasketId]
   ON c.[CartonId] = cc.[CartonId]
  OUTER APPLY (
    SELECT [Source] = (
      SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, l.[Location]) > 1 THEN ', ' ELSE '' END
       + CONVERT(VARCHAR(10), COUNT(*)) + ' egg(s) from ' + l.[Location]
      FROM [CartonContent] cc
       JOIN [Egg] e ON cc.[EggId] = e.[EggId]
       JOIN [Basket] b ON e.[BasketId] = b.[BasketId]
       JOIN [Location] l ON b.[LocationId] = l.[LocationId]
      WHERE cc.[CartonId] = c.[CartonId]
      GROUP BY l.[Location]
      ORDER BY COUNT(*) DESC, l.[Location]
      FOR XML PATH('')
     )
   ) s
GROUP BY c.[CartonId], c.[PackagedWhen], s.[Source]
GO

EXEC [LogBasketCollection] 1, 29, '2010-04-01'
EXEC [LogBasketCollection] 2, 53, '2010-04-02'
EXEC [SplitBaskets]
SELECT * FROM [Cartons] ORDER BY [CartonId]
EXEC [SplitCarton] 2, 4
SELECT * FROM [Cartons] ORDER BY [CartonId]
EXEC [ConsolidateCartons] 7, 8
SELECT * FROM [Cartons] ORDER BY [CartonId]

The solution above may be a bit of overkill, but it allows you to track the eggs back to their original basket/location.

Apologies for the formatting. Can anyone tell me the pseudo-tags to format a post?

Ok, so here's a "solution" that does what I threatened earler... not tracking cartons at all!

Technically, it doesn't meet the question requirements, but hey, I like it better than my first attempt.

Change the assumptions that we never split a carton unless the eggs are no good (they failed the UV test and had a chick in them, or they were too round or something.) In that case, you can remove as many as you like from a basket (till 0)

Make the assumption that as long as the basket is still in date (last 24 hours), that we can use them first to pack the next basket.

Then add the feature of being able to have different carton sized... I don't know about where you guys are from, but around here you can go to the market and get a tray of eggs (36 I think).

Anyway, make those changes and the solution looks like this:

IF OBJECT_ID ('Basket', 'U') IS NOT NULL
    DROP TABLE Basket;

GO
CREATE TABLE Basket
( id INT NOT NULL IDENTITY(1,1) CONSTRAINT Basket_pk PRIMARY KEY
, eggs INT NOT NULL
, carton_size INT NOT NULL
, cartons AS (eggs / carton_size)
, eggs_remaining AS (eggs % carton_size)
, collected DATETIME NOT NULL CONSTRAINT Basket_collected_def DEFAULT GETDATE()
)

GO
IF OBJECT_ID ('SplitBasket', 'P') IS NOT NULL
    DROP PROCEDURE SplitBasket;

GO
CREATE PROCEDURE SplitBasket
( @eggs INT
, @carton_size INT = 12
) AS
BEGIN
DECLARE @r TABLE (id INT, eggs INT)

UPDATE b
SET b.eggs = b.eggs - b.eggs_remaining
output deleted.id, deleted.eggs_remaining INTO @r
FROM Basket b
WHERE DATEDIFF(hour, b.collected, GETDATE()) <= 24
AND b.eggs_remaining > 0

DECLARE @rem TABLE (eggs INT)

INSERT INTO Basket (eggs, carton_size)
output inserted.eggs_remaining INTO @rem
SELECT ISNULL((SELECT SUM(eggs) FROM @r), 0) + @eggs, @carton_size

SELECT 'Use the ' + CAST(r.eggs AS NVARCHAR) + ' eggs from basket ' + CAST(r.id AS NVARCHAR)
FROM @r AS r
UNION ALL
SELECT 'Once finished, you should have ' + CAST((SELECT SUM(eggs) FROM @rem) AS NVARCHAR) + ' eggs remaining.'
END

GO
IF OBJECT_ID ('SplitCarton', 'P') IS NOT NULL
    DROP PROCEDURE SplitCarton;
GO
CREATE PROCEDURE SplitCarton
( @id INT
, @eggs_to_remove INT
) AS
BEGIN
DECLARE @rem TABLE (eggs INT, eggs_remaining INT)

UPDATE Basket
SET eggs = eggs - @eggs_to_remove
output inserted.eggs, inserted.eggs_remaining INTO @rem
WHERE id = @id
AND eggs >= @eggs_to_remove

IF EXISTS (SELECT * FROM @rem WHERE eggs = 0)
   SELECT 'The whole basket was ruined, nothing left of basket ' + CAST(@id AS NVARCHAR) + '!'
ELSE
   SELECT 'Once finished, you should have ' + CAST((SELECT SUM(eggs) FROM @rem) AS NVARCHAR) + ' eggs in a split carton.'
END

/*
-- ye old test script

exec SplitBasket 94

select * from Basket

exec SplitBasket 144
exec SplitBasket 169

select * from Basket

update Basket
set collected = dateadd(hour, -48, collected)

select * from Basket

exec SplitBasket 48
exec SplitBasket 71
exec SplitBasket 132

select * from Basket

exec SplitCarton 5, 8

select * from Basket

exec SplitBasket 101

select * from Basket

exec SplitCarton 5, 48

select * from Basket

exec SplitBasket 169, 36
select * from Basket
*/

Sigh, posting in IE, or Chrome with the [code] tags doesn't work... how are we supposed to make our postings look nice?

Oh yeah, Peso, I'm still not sure if you answered what happens if you do this:
exec SplitCarton 1, 4
exec SplitCarton 1, 4
Do you end up with 3 cartons with 4 in them, or one with 8 and two with 4? (if a, please explain how the output clause got to the variable... if b, where did the extra eggs come from?)

Sorry... just noticed a missing use case in the SplitCarton function:

Once again, please note that in the altered model, the assumption is that the only reason eggs are removed from a carton once packed is because they are no longer any good, so they are not put back in the pile for re-use.

I might also have to update the reporting from the splitBasket function to report the basket id, for labelling purposes... but you get the idea.

GO
IF OBJECT_ID ('SplitCarton', 'P') IS NOT NULL
    DROP PROCEDURE SplitCarton;
GO
CREATE PROCEDURE SplitCarton
( @id INT
, @eggs_to_remove INT
) AS
BEGIN
DECLARE @rem TABLE (eggs INT, eggs_remaining INT)

UPDATE Basket
SET eggs = eggs - @eggs_to_remove
output inserted.eggs, inserted.eggs_remaining INTO @rem
WHERE id = @id
AND eggs >= @eggs_to_remove

IF EXISTS (SELECT * FROM @rem)
BEGIN
   IF EXISTS (SELECT * FROM @rem WHERE eggs = 0)
     SELECT 'The whole basket was ruined, nothing left of basket ' + CAST(@id AS NVARCHAR) + '!'
   ELSE
     SELECT 'Once finished, you should have ' + CAST((SELECT SUM(eggs) FROM @rem) AS NVARCHAR) + ' eggs in a split carton.'
END
ELSE
   SELECT 'Can''t remove more eggs from a basket than it holds.'
END

I've had a lot of fun with this! I decided early on that I was going to make the 'bold' (one of my colleagues has described it as naively stupid) choice to have a 'Carton' table with 12 egg-holding columns, each of them being a foreign key to my 'Basket' table. A non-zero entry in any one of these columns indicates the presence of an egg, and the value indicates which basket it derives from. I did it this way because I think that the basket provenance of an egg is key, and I got confused as to how I could otherwise track eggs given the ability to arbitrarily consolidate and split cartons with eggs derived from different baskets. And there's just something appealing about modelling 12 egg-holders with 12 egg-holding columns.

I also decided to do it, as per the rubric, with low tech SQL. Luckily, my tech level isn't all that high.

The main problem I had was adding more eggs to a row where that row could have arbitry egg entries. But this became easy after I could shove all the eggs to the left hand columns of the table, leaving the right hand columns free for more eggs.

Oh, and I don't think that there's an 'empty carton' problem. The possibility of emptiness is inherent in cartons.


-- TABLE DEFINITIONS

-- Holds data about baskets. Could be updated with good data about time, basket filler, etc.
CREATE TABLE [dbo].[Basket]
(
[BasketID] [int] NOT NULL,
[NumberOfEggs] [int] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_Basket] PRIMARY KEY CLUSTERED
(
[BasketID] ASC
)
)
GO
ALTER TABLE [dbo].[Basket] WITH CHECK ADD CONSTRAINT [CK_Basket] CHECK (([NumberOfEggs]>=(0)))
GO
ALTER TABLE [dbo].[Basket] CHECK CONSTRAINT [CK_Basket]


-- Holds data about Cartons.
CREATE TABLE [dbo].[Carton]
(
[CartonID] [int] IDENTITY(1,1) NOT NULL,
[Box1] [int] NOT NULL CONSTRAINT [DF_Carton_Box1] DEFAULT ((0)),
[Box2] [int] NOT NULL CONSTRAINT [DF_Carton_Box2] DEFAULT ((0)),
[Box3] [int] NOT NULL CONSTRAINT [DF_Carton_Box3] DEFAULT ((0)),
[Box4] [int] NOT NULL CONSTRAINT [DF_Carton_Box4] DEFAULT ((0)),
[Box5] [int] NOT NULL CONSTRAINT [DF_Carton_Box5] DEFAULT ((0)),
[Box6] [int] NOT NULL CONSTRAINT [DF_Carton_Box6] DEFAULT ((0)),
[Box7] [int] NOT NULL CONSTRAINT [DF_Carton_Box7] DEFAULT ((0)),
[Box8] [int] NOT NULL CONSTRAINT [DF_Carton_Box8] DEFAULT ((0)),
[Box9] [int] NOT NULL CONSTRAINT [DF_Carton_Box9] DEFAULT ((0)),
[Box10] [int] NOT NULL CONSTRAINT [DF_Carton_Box10] DEFAULT ((0)),
[Box11] [int] NOT NULL CONSTRAINT [DF_Carton_Box11] DEFAULT ((0)),
[Box12] [int] NOT NULL CONSTRAINT [DF_Carton_Box12] DEFAULT ((0)),
CONSTRAINT [PK_Carton] PRIMARY KEY CLUSTERED
(
[CartonID] ASC
)
)

GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box1] FOREIGN KEY([Box1])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box1]
GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box10] FOREIGN KEY([Box10])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box10]
GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box11] FOREIGN KEY([Box11])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box11]
GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box12] FOREIGN KEY([Box12])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box12]
GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box2] FOREIGN KEY([Box2])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box2]
GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box3] FOREIGN KEY([Box3])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box3]
GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box4] FOREIGN KEY([Box4])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box4]
GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box5] FOREIGN KEY([Box5])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box5]
GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box6] FOREIGN KEY([Box6])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box6]
GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box7] FOREIGN KEY([Box7])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box7]
GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box8] FOREIGN KEY([Box8])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box8]
GO
ALTER TABLE [dbo].[Carton] WITH CHECK ADD CONSTRAINT [FK_Box9] FOREIGN KEY([Box9])
REFERENCES [dbo].[Basket] ([BasketID])
GO
ALTER TABLE [dbo].[Carton] CHECK CONSTRAINT [FK_Box9]

-- SUPPORT FUNCTIONALITY

-- Description: Counts the eggs in a Carton
CREATE FUNCTION [dbo].[NumberOfEggsInCarton]
(
@CartonID int
)
RETURNS int
AS
BEGIN

declare @count int
select @count =
case when Box1>0 then 1 else 0 end +
case when Box2>0 then 1 else 0 end +
case when Box3>0 then 1 else 0 end +
case when Box4>0 then 1 else 0 end +
case when Box5>0 then 1 else 0 end +
case when Box6>0 then 1 else 0 end +
case when Box7>0 then 1 else 0 end +
case when Box8>0 then 1 else 0 end +
case when Box9>0 then 1 else 0 end +
case when Box10>0 then 1 else 0 end +
case when Box11>0 then 1 else 0 end +
case when Box12>0 then 1 else 0 end
from Carton
where CartonID = @CartonID

return @count

END


-- Description: sends all the eggs in a carton to the left hand columns
-- (incidentally in order of size)
CREATE PROCEDURE [dbo].[SortCarton]
@CartonID int
AS
BEGIN

SET NOCOUNT ON;

update Carton set
Box1 = Box1Value,
Box2 = Box2Value,
Box3 = Box3Value,
Box4 = Box4Value,
Box5 = Box5Value,
Box6 = Box6Value,
Box7 = Box7Value,
Box8 = Box8Value,
Box9 = Box9Value,
Box10 = Box10Value,
Box11 = Box11Value,
Box12 = Box12Value
from
(
select
sum (case when ColumnNumber=1 then BoxValue else 0 end) as Box1Value,
sum (case when ColumnNumber=2 then BoxValue else 0 end) as Box2Value,
sum (case when ColumnNumber=3 then BoxValue else 0 end) as Box3Value,
sum (case when ColumnNumber=4 then BoxValue else 0 end) as Box4Value,
sum (case when ColumnNumber=5 then BoxValue else 0 end) as Box5Value,
sum (case when ColumnNumber=6 then BoxValue else 0 end) as Box6Value,
sum (case when ColumnNumber=7 then BoxValue else 0 end) as Box7Value,
sum (case when ColumnNumber=8 then BoxValue else 0 end) as Box8Value,
sum (case when ColumnNumber=9 then BoxValue else 0 end) as Box9Value,
sum (case when ColumnNumber=10 then BoxValue else 0 end) as Box10Value,
sum (case when ColumnNumber=11 then BoxValue else 0 end) as Box11Value,
sum (case when ColumnNumber=12 then BoxValue else 0 end) as Box12Value
from
(
select
T1.BoxValue, count(*) as ColumnNumber
from
(
select Box1 as BoxValue, 1 as Num from Carton where CartonID = @CartonID
union all
select Box2, 2 from Carton where CartonID = @CartonID
union all
select Box3, 3 from Carton where CartonID = @CartonID
union all
select Box4, 4 from Carton where CartonID = @CartonID
union all
select Box5, 5 from Carton where CartonID = @CartonID
union all
select Box6, 6 from Carton where CartonID = @CartonID
union all
select Box7, 7 from Carton where CartonID = @CartonID
union all
select Box8, 8 from Carton where CartonID = @CartonID
union all
select Box9, 9 from Carton where CartonID = @CartonID
union all
select Box10, 10 from Carton where CartonID = @CartonID
union all
select Box11, 11 from Carton where CartonID = @CartonID
union all
select Box12, 12 from Carton where CartonID = @CartonID
) T1
inner join
(
select Box1 as BoxValue, 1 as Num from Carton where CartonID = @CartonID
union all
select Box2, 2 from Carton where CartonID = @CartonID
union all
select Box3, 3 from Carton where CartonID = @CartonID
union all
select Box4, 4 from Carton where CartonID = @CartonID
union all
select Box5, 5 from Carton where CartonID = @CartonID
union all
select Box6, 6 from Carton where CartonID = @CartonID
union all
select Box7, 7 from Carton where CartonID = @CartonID
union all
select Box8, 8 from Carton where CartonID = @CartonID
union all
select Box9, 9 from Carton where CartonID = @CartonID
union all
select Box10, 10 from Carton where CartonID = @CartonID
union all
select Box11, 11 from Carton where CartonID = @CartonID
union all
select Box12, 12 from Carton where CartonID = @CartonID
) T2
on T1.BoxValue < T2.BoxValue or (T1.BoxValue=T2.BoxValue and T1.Num <= T2.Num)
group by T1.BoxValue, T1.Num
) T
) T
where CartonID = @CartonID

END

-- THE THREE REQUIRED PROCS

-- takes the number of eggs in a basket and creates appropriate cartons
-- doesn't then zero the number of eggs in the basket, though it could
CREATE PROCEDURE [dbo].[SplitBasket]
@basketID int
AS
BEGIN

begin transaction

-- get the correct number of eggs for the basket
declare @i int
select @i = NumberOfEggs from Basket where BasketID = @BasketID

-- test for a positive number
if @i is null or @i<=0
begin
rollback transaction
return -1
end

-- insert the eggs into cartons
insert into Carton (Box1, Box2, Box3, Box4, Box5, Box6, Box7, Box8, Box9, Box10, Box11, Box12)
select
case when ((N.Num-1)*12)<@i then @basketID else 0 end as Box1,
case when ((N.Num-1)*12)+1<@i then @basketID else 0 end as Box2,
case when ((N.Num-1)*12)+2<@i then @basketID else 0 end as Box3,
case when ((N.Num-1)*12)+3<@i then @basketID else 0 end as Box4,
case when ((N.Num-1)*12)+4<@i then @basketID else 0 end as Box5,
case when ((N.Num-1)*12)+5<@i then @basketID else 0 end as Box6,
case when ((N.Num-1)*12)+6<@i then @basketID else 0 end as Box7,
case when ((N.Num-1)*12)+7<@i then @basketID else 0 end as Box8,
case when ((N.Num-1)*12)+8<@i then @basketID else 0 end as Box9,
case when ((N.Num-1)*12)+9<@i then @basketID else 0 end as Box10,
case when ((N.Num-1)*12)+10<@i then @basketID else 0 end as Box11,
case when ((N.Num-1)*12)+11<@i then @basketID else 0 end as Box12
from
Numbers N
where N.Num<=ceiling((@i+11)/12)

commit transaction

END


-- moves a certain number of eggs from a carton to another
-- assuming that this makes sense
CREATE PROCEDURE [dbo].[ConsolidateCartons]
@fromCartonID int,
@toCartonID int,
@eggCount int
AS
BEGIN
SET NOCOUNT ON;

-- check that we have a positive egg count
if @eggCount <= 0
begin
return -1
end

begin transaction

declare @numTo int
select @numTo = dbo.NumberOfEggsInCarton(@toCartonID)

-- check that the carton exists
if @numTo<0
begin
rollback transaction
return -1
end

-- check that the total number of eggs will not exceed 12
if (@numTo + @eggCount)>12
begin
rollback transaction
return -1
end

declare @numFrom int
select @numFrom = dbo.NumberOfEggsInCarton(@fromCartonID)

-- check that the carton exists
if @numFrom<0
begin
rollback transaction
return -1
end

-- check that the carton has enough eggs to give
if @numFrom < @eggCount
begin
rollback transaction
return -1
end

-- rearrange the cartons
exec dbo.SortCarton @fromCartonID
exec dbo.SortCarton @toCartonID

-- move eggs from the front of the @fromCarton to the back of the @toCarton
update Carton set
Box12 = case when @eggCount>0 then T.Box1 else Carton.Box12 end,
Box11 = case when @eggCount>1 then T.Box2 else Carton.Box11 end,
Box10 = case when @eggCount>2 then T.Box3 else Carton.Box10 end,
Box9 = case when @eggCount>3 then T.Box4 else Carton.Box9 end,
Box8 = case when @eggCount>4 then T.Box5 else Carton.Box8 end,
Box7 = case when @eggCount>5 then T.Box6 else Carton.Box7 end,
Box6 = case when @eggCount>6 then T.Box7 else Carton.Box6 end,
Box5 = case when @eggCount>7 then T.Box8 else Carton.Box5 end,
Box4 = case when @eggCount>8 then T.Box9 else Carton.Box4 end,
Box3 = case when @eggCount>9 then T.Box10 else Carton.Box3 end,
Box2 = case when @eggCount>10 then T.Box11 else Carton.Box2 end,
Box1 = case when @eggCount>11 then T.Box12 else Carton.Box1 end
from
(
select * from Carton where CartonID = @fromCartonID
) T
where Carton.CartonID = @toCartonID

update Carton set
Box1 = case when @eggCount>0 then 0 else Box1 end,
Box2 = case when @eggCount>1 then 0 else Box2 end,
Box3 = case when @eggCount>2 then 0 else Box3 end,
Box4 = case when @eggCount>3 then 0 else Box4 end,
Box5 = case when @eggCount>4 then 0 else Box5 end,
Box6 = case when @eggCount>5 then 0 else Box6 end,
Box7 = case when @eggCount>6 then 0 else Box7 end,
Box8 = case when @eggCount>7 then 0 else Box8 end,
Box9 = case when @eggCount>8 then 0 else Box9 end,
Box10 = case when @eggCount>9 then 0 else Box10 end,
Box11 = case when @eggCount>10 then 0 else Box11 end,
Box12 = case when @eggCount>11 then 0 else Box12 end
from Carton where CartonID = @fromCartonID

commit tran
END


-- takes a number of eggs from one carton and places them
-- in a new carton
CREATE PROCEDURE [dbo].[SplitCarton]
@fromCartonID int,
@eggCount int
AS
BEGIN

-- check that we have a positive egg count
if @eggCount <= 0
begin
return -1
end

begin transaction

declare @numFrom int
select @numFrom = dbo.NumberOfEggsInCarton(@fromCartonID)

-- check that the carton exists
if @numFrom<0
begin
rollback transaction
return -1
end

-- check that the carton has enough eggs to give
if @numFrom < @eggCount
begin
rollback transaction
return -1
end

-- rearrange the carton
exec dbo.SortCarton @fromCartonID

-- create a new carton with our entries
insert into Carton (Box1, Box2, Box3, Box4, Box5, Box6, Box7, Box8, Box9, Box10, Box11, Box12)
select
case when @eggCount>0 then Box1 else 0 end,
case when @eggCount>1 then Box2 else 0 end,
case when @eggCount>2 then Box3 else 0 end,
case when @eggCount>3 then Box4 else 0 end,
case when @eggCount>4 then Box5 else 0 end,
case when @eggCount>5 then Box6 else 0 end,
case when @eggCount>6 then Box7 else 0 end,
case when @eggCount>7 then Box8 else 0 end,
case when @eggCount>8 then Box9 else 0 end,
case when @eggCount>9 then Box10 else 0 end,
case when @eggCount>10 then Box11 else 0 end,
case when @eggCount>11 then Box12 else 0 end
from Carton where CartonID = @fromCartonID


-- blank eggs from the front of @fromCarton
update Carton set
Box1 = case when @eggCount>0 then 0 else Box1 end,
Box2 = case when @eggCount>1 then 0 else Box2 end,
Box3 = case when @eggCount>2 then 0 else Box3 end,
Box4 = case when @eggCount>3 then 0 else Box4 end,
Box5 = case when @eggCount>4 then 0 else Box5 end,
Box6 = case when @eggCount>5 then 0 else Box6 end,
Box7 = case when @eggCount>6 then 0 else Box7 end,
Box8 = case when @eggCount>7 then 0 else Box8 end,
Box9 = case when @eggCount>8 then 0 else Box9 end,
Box10 = case when @eggCount>9 then 0 else Box10 end,
Box11 = case when @eggCount>10 then 0 else Box11 end,
Box12 = case when @eggCount>11 then 0 else Box12 end
from Carton where CartonID = @fromCartonID

commit tran

END

Let's just say, I'm really sorry for all those who came to the discussion late!

Thanks Puz.

Oh, I also forgot the following points in my posts: I have a zero-keyed entry in the basket table to act as the foreign key reference for 'empty' boxes, and I make use of the standard table of consecutive integers which I called Numbers.

I have no idea whether the formatting will survive... guess I'll see. :)

In the interests of blog comment space, I did not put in any of the stuff I would normally put in: TRY CATCH, transaction handling, parameter checking, etc. The statements in the procedures will not return errors, but they won't insert bad data either. If you enter parameters that won't work (trying to consolidate 2 cartons with more than 12 eggs between them for example) the procedure just won't do anything.

-----------
CREATE TABLE Locations
(LocationID VARCHAR(20) NOT NULL,
LocationDescription VARCHAR(100) NOT NULL,
CONSTRAINT PK_Locations PRIMARY KEY (LocationID))

CREATE TABLE Baskets
(BasketID VARCHAR(20) NOT NULL,
BasketEggCount INT NOT NULL,
CONSTRAINT PK_Baskets PRIMARY KEY (BasketID))

CREATE TABLE Cartons(
BasketID VARCHAR(20) NOT NULL,
CartonSequenceNo INT NOT NULL,
CartonEggCount TINYINT NOT NULL DEFAULT (0),
CONSTRAINT PK_Cartons PRIMARY KEY (BasketID, CartonSequenceNo),
CONSTRAINT FK_CartonsBaskets FOREIGN KEY (BasketID) REFERENCES dbo.Baskets(BasketID),
CONSTRAINT CK_CartonEggCount CHECK (CartonEggCount BETWEEN 0 AND 12)
)

CREATE TABLE BasketLocations(
LocationID VARCHAR(20) NOT NULL,
BasketID VARCHAR(20) NOT NULL,
CONSTRAINT PK_BasketLocations PRIMARY KEY (LocationID, BasketID),
CONSTRAINT FK_BasketLocations_Locations FOREIGN KEY (LocationID) REFERENCES Locations(LocationID),
CONSTRAINT FK_BasketLocations_Baskets FOREIGN KEY (BasketID) REFERENCES Baskets(BasketID)
)

CREATE TABLE Integers(I INT NOT NULL,
CONSTRAINT PK_Integers PRIMARY KEY (I))

INSERT Integers(I)
SELECT (COALESCE(MAX(I), 0) + 1)
FROM Integers
GO 500


INSERT Locations (LocationID, LocationDescription)
SELECT LOC.LocationID, LOC.LocationDescription
FROM ( VALUES ('BARN1', 'Barn #1'),
('BARN2', 'Barn #2'),
('PLANT1', 'Processing Plant #1')
) AS LOC(LocationID, LocationDescription)
WHERE NOT EXISTS(
SELECT *
FROM Locations
WHERE LocationID = LOC.LocationID)

INSERT Baskets(BasketID, BasketEggCount)
SELECT BAS.BasketID, BAS.BasketEggCount
FROM (VALUES ('BASKET1', 0),
('BASKET2', 11),
('BASKET3', 25),
('BASKET4', 144),
('BASKET5', 0)
) AS BAS (BasketID, BasketEggCount)
WHERE NOT EXISTS (
SELECT *
FROM Baskets
WHERE BasketID = BAS.BasketID)

INSERT BasketLocations(BasketID, LocationID)
SELECT B.BasketID,
L.LocationID
FROM Baskets B
CROSS JOIN Locations L
WHERE B.BasketID <> 'BASKET5'
AND L.LocationID = 'PLANT1'
AND NOT EXISTS (
SELECT *
FROM BasketLocations
WHERE BasketID = B.BasketID
AND LocationID = L.LocationID )

INSERT BasketLocations(BasketID, LocationID)
SELECT B.BasketID,
L.LocationID
FROM Baskets B
CROSS JOIN Locations L
WHERE B.BasketID = 'BASKET5'
AND L.LocationID = 'BARN1'
AND NOT EXISTS (
SELECT *
FROM BasketLocations
WHERE BasketID = B.BasketID
AND LocationID = L.LocationID )
GO

CREATE PROCEDURE dbo.SplitBaskets(
@LocationID VARCHAR(20)
)
AS
BEGIN
-- Splits all Baskets at @Location into Cartons for any Basket
-- where the BasketEggCount > 0

DECLARE @CartonSize TINYINT

SET @CartonSize = 12

INSERT Cartons(BasketID, CartonSequenceNo, CartonEggCount)
SELECT X.BasketID,
X.SequenceNo,
CASE WHEN X.CumEggs <= X.BasketEggCount THEN @CartonSize
ELSE X.Remainder END
AS CartonEggCount
FROM (-- produces a set of rows with CartonEggCount = @CartonSize
-- the last row gets CartonEggCount = Remainder
-- sequence numbers are generated
-- doesn't insert rows with BasketEggCount = 0
SELECT ROW_NUMBER() OVER (PARTITION BY B.BasketID ORDER BY B.BasketID)
AS SequenceNo,
B.BasketEggCount,
I.i AS CumEggs,
B.basketeggcount % @CartonSize
AS Remainder,
B.BasketID
FROM Baskets B
JOIN BasketLocations L
ON B.BasketID = L.BasketID
CROSS JOIN Integers I
WHERE I.I <= (B.BasketEggCount + @CartonSize)
AND I.I % @CartonSize = 0
AND L.LocationID = @LocationID
AND B.BasketEggCount > 0
AND I.I - B.BasketEggCount <> @CartonSize
)X -- X, get it? Eggs/X?
WHERE NOT EXISTS(
SELECT *
FROM Cartons
WHERE BasketID = X.BasketID
AND SequenceNo = X.SequenceNo)
END
GO

CREATE PROCEDURE dbo.SplitCarton(
@BasketID VARCHAR(20),
@CartonSequenceNo INT,
@EggsToMove INT )

AS
BEGIN
-- Takes a Carton identified by @BasketID and @CartonSequenceNo
-- and removes @EggsToMove eggs from it.
-- creates a new Carton with @EggsToMove eggs in it.

MERGE INTO Cartons C
USING ( SELECT C.BasketID,
(SELECT MAX(CartonSequenceNo) + 1
FROM Cartons
WHERE BasketID = C.BasketID) AS CartonSequenceNo,
@EggsToMove AS CartonEggCount
FROM Cartons C
WHERE C.BasketID = @BasketID
AND C.CartonSequenceNo = @CartonSequenceNo
AND C.CartonEggCount > @EggsToMove
AND @EggsToMove BETWEEN 1 AND (C.CartonEggCount - 1)
UNION ALL
SELECT BasketID,
CartonSequenceNo,
CartonEggCount - @EggsToMove AS NewCartonEggCount
FROM Cartons
WHERE BasketID = @BasketID
AND CartonSequenceNo = @CartonSequenceNo
AND CartonEggCount > @EggsToMove
AND @EggsToMove BETWEEN 1 AND (CartonEggCount - 1)
) NEW
ON C.BasketID = NEW.BasketID
AND C.CartonSequenceNo = NEW.CartonSequenceNo
WHEN MATCHED THEN UPDATE
SET C.CartonEggCount = NEW.CartonEggCount
WHEN NOT MATCHED BY TARGET THEN INSERT --(BasketID, CartonSequenceNo, CartonEggCount)
VALUES(NEW.BasketID, NEW.CartonSequenceNo, NEW.CartonEggCount)
;
END
GO

CREATE PROCEDURE dbo.ConsolidateCartons(
@BasketIDFrom VARCHAR(20),
@CartonSequenceNoFrom INT,
@BasketIDTo VARCHAR(20),
@CartonSequenceNoTo INT)
AS
BEGIN
-- Takes all the eggs from Carton identified by @BasketIDFrom and @CartonSequenceNoFrom
-- and adds them to the count for Carton identified by @BasketIDTo and @CartonSequenceNoTo
-- then deletes the empty Carton

-- If this were production code, there would be parameter checking, TRY CATCH blocks, transactions, etc.

DECLARE @CartonSize INT
SET @CartonSize = 12

MERGE INTO Cartons C
USING( -- builds a set of rows that contain what the database
-- should look like after the move
-- both parts of the query check that the sum of the two
-- egg counts do not exceed the maximum carton size - if they
-- do, this is an invalid consolidation and no rows will
-- be updated or deleted
SELECT CTo.BasketID,
CTo.CartonSequenceNo,
CTo.CartonEggCount + CFrom.CartonEggCount AS CartonEggCount
FROM Cartons CFrom
JOIN Cartons CTo
ON CFrom.BasketID = @BasketIDFrom
AND CFrom.CartonSequenceNo = @CartonSequenceNoFrom
AND CTo.BasketID = @BasketIDTo
AND CTo.CartonSequenceNo = @CartonSequenceNoTo
WHERE CTo.CartonEggCount + CFrom.CartonEggCount <= @CartonSize
UNION ALL
-- because the rule is that all eggs must be moved from the FROM
-- Carton, this set contains 0 as the CartonEggCount
SELECT CFrom.BasketID,
CFrom.CartonSequenceNo,
0 AS CartonEggCount
FROM Cartons CFrom
JOIN Cartons CTo
ON CFrom.BasketID = @BasketIDFrom
AND CFrom.CartonSequenceNo = @CartonSequenceNoFrom
AND CTo.CartonSequenceNo = @CartonSequenceNoTo
WHERE CTo.CartonEggCount + CFrom.CartonEggCount <= @CartonSize
) NEW
ON C.BasketID = NEW.BasketID
AND C.CartonSequenceNo = NEW.CartonSequenceNo
-- if NEW.CartonEggCount > 0, this must be the Carton that the eggs are moving to
WHEN MATCHED AND NEW.CartonEggCount > 0 THEN UPDATE
SET C.CartonEggCount = NEW.CartonEggCount
-- if NEW.CartonEggCount = 0, this must be the Carton that is being removed
WHEN MATCHED AND NEW.CartonEggCount = 0 THEN DELETE;

END
GO