A blog about SQL and MS SQL Server

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