Every post in the Production SQL series queries the same database: CellarDB, a mid-size California winery. Estate and purchased fruit come in, wine lots move through barrels and blends, and bulk wine goes out on contracts that get amended after signature. This page builds it. Run the script once and you're set for the whole series.
Why a winery instead of the usual orders-and-customers store? Because the hard problems this series teaches arise from this data on their own. A blend draws from many wine lots. A lot fills many barrels, and a barrel is filled many times across vintages. A contract's terms change months after the ink dries. Nothing has to be contrived; the structure produces the traps. I also spent a decade around data shaped like this, which means the examples can be honest about how it behaves.
What you need
The same two pieces as ever: an engine and a client.
- SQL Server 2022 Developer Edition, free and full-featured, from the SQL Server downloads page. Basic installation, default settings.
- SQL Server Management Studio (SSMS), from the SSMS download page. Connect with Windows Authentication.
On Mac or Linux, run the mcr.microsoft.com/mssql/server:2022-latest Docker image and connect with Azure Data Studio or any SQL client. Everything in the series is plain T-SQL; the client doesn't matter.
The tables, by grain
The single most useful thing to know about any table is what one row means. Here is the answer for all twelve, up front:
VineyardBlocks: one row per vineyard block (~40 rows)FruitLots: one row per fruit delivery: a block, a vintage, a pick (~330 rows)WineLots: one row per wine lot in the cellar (~335 rows)Blends: one row per finished blend (~60 rows)BlendComponents: one row per blend-and-wine-lot pairing (~280 rows)Barrels: one row per physical barrel (~900 rows)BarrelFills: one row per barrel-fill event, not per barrel and not per lot (~2,600 rows)WorkOrders: one row per cellar operation logged against a lot (~5,000 rows)Customers: one row per bulk-wine buyer (25 rows)BulkContracts: one row per contract, holding the original terms only (~45 rows)ContractAmendments: one row per amendment to a contract (~30 rows)Shipments: one row per shipment against a contract (~100 rows)
Row counts are approximate by design; the generators target those sizes.
A note on the data
The script is deterministic. There is no RAND() and no NEWID() anywhere in it; every "random" value is computed arithmetically from stable inputs. Run it twice, or on two machines, and you get the same database both times. When a later post says a query returns a particular result, yours will match.
⚠️
The data is imperfect on purpose. There are missing lab numbers, a double-entered record, and a contract whose terms changed twice after signature. The comments in the script mark these as deliberate wrinkles. Later posts in the series depend on every one of them, so resist the urge to clean anything.
The setup script
Run the whole thing in one go. It drops and rebuilds, so it's safe to re-run.
-- CellarDB setup script
-- Companion to the Production SQL series. Target: SQL Server 2022.
-- Deterministic: no RAND(), no NEWID(). Re-running produces the same data.
-- GENERATE_SERIES requires database compatibility level 160, which is the
-- default for a database created on SQL Server 2022.
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = 'CellarDB')
CREATE DATABASE CellarDB;
GO
USE CellarDB;
GO
DROP TABLE IF EXISTS Shipments, ContractAmendments, BulkContracts, Customers,
WorkOrders, BarrelFills, Barrels, BlendComponents,
Blends, WineLots, FruitLots, VineyardBlocks;
GO
-- ---------------------------------------------------------------------------
-- Tables
-- ---------------------------------------------------------------------------
CREATE TABLE VineyardBlocks ( -- one row per vineyard block
BlockID INT IDENTITY(1,1) PRIMARY KEY,
BlockCode VARCHAR(12) NOT NULL UNIQUE,
VineyardName NVARCHAR(60) NOT NULL,
Region NVARCHAR(40) NOT NULL,
Variety NVARCHAR(30) NOT NULL,
Acres DECIMAL(5,2) NOT NULL,
PlantedYear SMALLINT NULL -- unknown for two leased blocks
);
CREATE TABLE FruitLots ( -- one row per fruit delivery (block x vintage x pick)
FruitLotID INT IDENTITY(1,1) PRIMARY KEY,
BlockID INT NOT NULL REFERENCES VineyardBlocks (BlockID),
Vintage SMALLINT NOT NULL,
PickDate DATE NOT NULL,
Tons DECIMAL(6,2) NOT NULL,
Brix DECIMAL(4,1) NULL, -- NULL = arrived unsampled
pH DECIMAL(3,2) NULL
);
CREATE TABLE WineLots ( -- one row per wine lot in the cellar
WineLotID INT IDENTITY(1,1) PRIMARY KEY,
LotCode VARCHAR(16) NOT NULL UNIQUE,
FruitLotID INT NULL REFERENCES FruitLots (FruitLotID), -- NULL = purchased bulk wine
Vintage SMALLINT NOT NULL,
Variety NVARCHAR(30) NOT NULL,
VolumeGal DECIMAL(8,1) NOT NULL,
Status VARCHAR(12) NOT NULL -- Aging | Blended | Sold | Dumped
);
CREATE TABLE Blends ( -- one row per finished blend
BlendID INT IDENTITY(1,1) PRIMARY KEY,
BlendCode VARCHAR(16) NOT NULL UNIQUE,
BlendName NVARCHAR(60) NOT NULL,
Vintage SMALLINT NOT NULL,
TargetVolumeGal DECIMAL(8,1) NOT NULL,
BottlingDate DATE NULL -- NULL = not yet bottled
);
CREATE TABLE BlendComponents ( -- one row per (blend, wine lot) component
BlendID INT NOT NULL REFERENCES Blends (BlendID),
WineLotID INT NOT NULL REFERENCES WineLots (WineLotID),
ComponentPct DECIMAL(5,2) NULL,
VolumeUsedGal DECIMAL(8,1) NOT NULL,
PRIMARY KEY (BlendID, WineLotID)
);
CREATE TABLE Barrels ( -- one row per physical barrel
BarrelID INT IDENTITY(1,1) PRIMARY KEY,
BarrelCode VARCHAR(12) NOT NULL UNIQUE,
Cooper NVARCHAR(40) NOT NULL,
Origin NVARCHAR(30) NULL, -- French | American | Hungarian
ToastLevel VARCHAR(8) NULL,
CapacityGal DECIMAL(6,1) NOT NULL, -- mostly 59.4 (225 L)
FirstFillYear SMALLINT NOT NULL
);
CREATE TABLE BarrelFills ( -- one row per barrel-fill EVENT
BarrelFillID INT IDENTITY(1,1) PRIMARY KEY,
BarrelID INT NOT NULL REFERENCES Barrels (BarrelID),
WineLotID INT NOT NULL REFERENCES WineLots (WineLotID),
FillDate DATE NOT NULL,
EmptyDate DATE NULL, -- NULL = still in barrel
FillVolumeGal DECIMAL(6,1) NOT NULL
);
CREATE TABLE WorkOrders ( -- one row per cellar operation logged
WorkOrderID INT IDENTITY(1,1) PRIMARY KEY,
WineLotID INT NOT NULL REFERENCES WineLots (WineLotID),
WorkType VARCHAR(20) NOT NULL, -- PumpOver | Racking | SO2Add | Topping | Filtration | Transfer
ScheduledDate DATE NOT NULL,
CompletedDate DATE NULL, -- NULL = open
CellarHand NVARCHAR(40) NULL,
LaborHours DECIMAL(4,1) NULL
);
CREATE TABLE Customers ( -- one row per bulk-wine buyer
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
CustomerName NVARCHAR(60) NOT NULL,
State CHAR(2) NULL,
CustomerType VARCHAR(12) NOT NULL -- Winery | Negociant | Broker
);
CREATE TABLE BulkContracts ( -- one row per contract, ORIGINAL terms
ContractID INT IDENTITY(1,1) PRIMARY KEY,
ContractNumber VARCHAR(12) NOT NULL UNIQUE,
CustomerID INT NOT NULL REFERENCES Customers (CustomerID),
ContractDate DATE NOT NULL,
Variety NVARCHAR(30) NOT NULL,
Vintage SMALLINT NOT NULL,
VolumeGal DECIMAL(8,1) NOT NULL,
PricePerGal DECIMAL(8,2) NOT NULL,
Status VARCHAR(12) NOT NULL -- Open | Fulfilled | Cancelled
);
CREATE TABLE ContractAmendments ( -- one row per amendment
AmendmentID INT IDENTITY(1,1) PRIMARY KEY,
ContractID INT NOT NULL REFERENCES BulkContracts (ContractID),
AmendmentDate DATE NOT NULL,
NewVolumeGal DECIMAL(8,1) NULL, -- NULL = volume unchanged
NewPricePerGal DECIMAL(8,2) NULL, -- NULL = price unchanged
Reason NVARCHAR(100) NULL
);
CREATE TABLE Shipments ( -- one row per shipment against a contract
ShipmentID INT IDENTITY(1,1) PRIMARY KEY,
ContractID INT NOT NULL REFERENCES BulkContracts (ContractID),
ShipDate DATE NOT NULL,
VolumeGal DECIMAL(8,1) NOT NULL,
CarrierRef VARCHAR(20) NULL
);
GO
-- ---------------------------------------------------------------------------
-- VineyardBlocks: 40 blocks across seven vineyards.
-- ---------------------------------------------------------------------------
INSERT INTO VineyardBlocks (BlockCode, VineyardName, Region, Variety, Acres, PlantedYear)
VALUES
('HR-01', N'Home Ranch', N'Russian River Valley', N'Pinot Noir', 6.20, 1999),
('HR-02', N'Home Ranch', N'Russian River Valley', N'Pinot Noir', 4.80, 1999),
('HR-03', N'Home Ranch', N'Russian River Valley', N'Pinot Noir', 7.50, 2004),
('HR-04', N'Home Ranch', N'Russian River Valley', N'Pinot Noir', 5.10, 2010),
('HR-05', N'Home Ranch', N'Russian River Valley', N'Chardonnay', 8.40, 1997),
('HR-06', N'Home Ranch', N'Russian River Valley', N'Chardonnay', 6.60, 2002),
('HR-07', N'Home Ranch', N'Russian River Valley', N'Pinot Noir', 5.90, 2012),
('HR-08', N'Home Ranch', N'Russian River Valley', N'Chardonnay', 4.20, 2015),
('RB-01', N'River Bend', N'Russian River Valley', N'Chardonnay', 9.10, 2001),
('RB-02', N'River Bend', N'Russian River Valley', N'Chardonnay', 7.30, 2005),
('RB-03', N'River Bend', N'Russian River Valley', N'Pinot Noir', 6.80, 2008),
('RB-04', N'River Bend', N'Russian River Valley', N'Pinot Noir', 5.40, 2008),
('RB-05', N'River Bend', N'Russian River Valley', N'Syrah', 4.60, 2011),
('RB-06', N'River Bend', N'Russian River Valley', N'Chardonnay', 8.80, 2016),
('SC-01', N'Stone Corral', N'Alexander Valley', N'Cabernet Sauvignon', 10.20, 1996),
('SC-02', N'Stone Corral', N'Alexander Valley', N'Cabernet Sauvignon', 8.70, 2000),
('SC-03', N'Stone Corral', N'Alexander Valley', N'Cabernet Sauvignon', 7.90, 2003),
('SC-04', N'Stone Corral', N'Alexander Valley', N'Merlot', 6.30, 2003),
('SC-05', N'Stone Corral', N'Alexander Valley', N'Merlot', 5.80, 2009),
('SC-06', N'Stone Corral', N'Alexander Valley', N'Cabernet Sauvignon', 9.40, 2014),
('DB-01', N'Dry Creek Bench', N'Dry Creek Valley', N'Zinfandel', 7.10, 1989),
('DB-02', N'Dry Creek Bench', N'Dry Creek Valley', N'Zinfandel', 5.50, 1992),
('DB-03', N'Dry Creek Bench', N'Dry Creek Valley', N'Zinfandel', 6.00, 2001),
('DB-04', N'Dry Creek Bench', N'Dry Creek Valley', N'Petite Sirah', 4.40, 2006),
('DB-05', N'Dry Creek Bench', N'Dry Creek Valley', N'Petite Sirah', 3.90, 2013),
('ET-01', N'Eastside Terrace', N'Sonoma Coast', N'Pinot Noir', 5.20, 2007),
('ET-02', N'Eastside Terrace', N'Sonoma Coast', N'Pinot Noir', 6.70, 2007),
('ET-03', N'Eastside Terrace', N'Sonoma Coast', N'Chardonnay', 7.00, 2010),
('ET-04', N'Eastside Terrace', N'Sonoma Coast', N'Syrah', 4.10, 2012),
('ET-05', N'Eastside Terrace', N'Sonoma Coast', N'Pinot Noir', 5.60, 2017),
('OL-01', N'Olsen Lease', N'Clarksburg', N'Chardonnay', 11.30, 2003),
('OL-02', N'Olsen Lease', N'Clarksburg', N'Chardonnay', 9.60, NULL), -- leased; planting year unknown
('OL-03', N'Olsen Lease', N'Clarksburg', N'Sauvignon Blanc', 8.20, 2008),
('OL-04', N'Olsen Lease', N'Clarksburg', N'Sauvignon Blanc', 7.70, 2011),
('KL-01', N'Kettleman Lease', N'Lodi', N'Zinfandel', 10.80, 1998),
('KL-02', N'Kettleman Lease', N'Lodi', N'Zinfandel', 9.20, 2002),
('KL-03', N'Kettleman Lease', N'Lodi', N'Merlot', 8.50, 2005),
('KL-04', N'Kettleman Lease', N'Lodi', N'Petite Sirah', 6.90, NULL), -- leased; planting year unknown
('KL-05', N'Kettleman Lease', N'Lodi', N'Zinfandel', 7.40, 2009),
('KL-06', N'Kettleman Lease', N'Lodi', N'Merlot', 5.30, 2012);
GO
-- ---------------------------------------------------------------------------
-- FruitLots: every block delivers each vintage 2019-2024; about a third of
-- block-vintages get a second pick. ORDER BY keeps identity assignment stable.
-- ---------------------------------------------------------------------------
INSERT INTO FruitLots (BlockID, Vintage, PickDate, Tons, Brix, pH)
SELECT
b.BlockID,
v.value,
DATEADD(DAY,
ABS(CHECKSUM('pick', b.BlockCode, v.value, p.value)) % 45 + (p.value - 1) * 6,
DATEFROMPARTS(v.value, 8, 15)),
CAST(b.Acres * (2.6 + ABS(CHECKSUM('yield', b.BlockCode, v.value, p.value)) % 26 / 10.0)
/ p.value AS DECIMAL(6,2)),
-- Deliberate wrinkle: night machine picks arrive unsampled (NULL Brix/pH).
CASE WHEN ABS(CHECKSUM('night', b.BlockCode, v.value, p.value)) % 13 = 0 THEN NULL
ELSE CAST(21.8 + ABS(CHECKSUM('brix', b.BlockCode, v.value, p.value)) % 48 / 10.0 AS DECIMAL(4,1))
END,
CASE WHEN ABS(CHECKSUM('night', b.BlockCode, v.value, p.value)) % 13 = 0 THEN NULL
ELSE CAST(3.28 + ABS(CHECKSUM('ph', b.BlockCode, v.value, p.value)) % 58 / 100.0 AS DECIMAL(3,2))
END
FROM VineyardBlocks b
CROSS JOIN GENERATE_SERIES(2019, 2024) v
CROSS APPLY GENERATE_SERIES(1,
CASE WHEN ABS(CHECKSUM('twopick', b.BlockCode, v.value)) % 3 = 0 THEN 2 ELSE 1 END) p
ORDER BY b.BlockCode, v.value, p.value;
GO
-- ---------------------------------------------------------------------------
-- WineLots: every delivery ferments as its own lot, at 150-170 gallons per
-- ton (post 9's sanity band rides on that). Plus 15 purchased bulk lots with
-- no fruit lot behind them. The handful of deliveries that never became wine
-- are planted explicitly after this block.
-- ---------------------------------------------------------------------------
WITH RankedFruit AS (
SELECT f.FruitLotID, f.BlockID, f.Vintage, f.Tons,
ROW_NUMBER() OVER (PARTITION BY f.BlockID, f.Vintage ORDER BY f.PickDate) AS PickNo
FROM FruitLots f
)
INSERT INTO WineLots (LotCode, FruitLotID, Vintage, Variety, VolumeGal, Status)
SELECT
CONCAT(RIGHT(CAST(rf.Vintage AS VARCHAR(4)), 2), '-',
CASE b.Variety
WHEN N'Pinot Noir' THEN 'PN'
WHEN N'Chardonnay' THEN 'CH'
WHEN N'Cabernet Sauvignon' THEN 'CS'
WHEN N'Merlot' THEN 'ME'
WHEN N'Zinfandel' THEN 'ZN'
WHEN N'Petite Sirah' THEN 'PS'
WHEN N'Syrah' THEN 'SY'
ELSE 'SB'
END,
'-', REPLACE(b.BlockCode, '-', ''),
CASE WHEN rf.PickNo = 2 THEN 'B' ELSE '' END),
rf.FruitLotID,
rf.Vintage,
b.Variety,
CAST(rf.Tons * (150 + ABS(CHECKSUM('gpt', b.BlockCode, rf.Vintage, rf.PickNo)) % 21) AS DECIMAL(8,1)),
CASE
WHEN rf.Vintage >= 2023 THEN 'Aging'
WHEN ABS(CHECKSUM('status', b.BlockCode, rf.Vintage, rf.PickNo)) % 10 <= 5 THEN 'Blended'
WHEN ABS(CHECKSUM('status', b.BlockCode, rf.Vintage, rf.PickNo)) % 10 <= 8 THEN 'Sold'
ELSE 'Dumped'
END
FROM RankedFruit rf
JOIN VineyardBlocks b ON b.BlockID = rf.BlockID
ORDER BY rf.Vintage, b.BlockCode, rf.PickNo;
GO
-- Deliberate wrinkle: purchased bulk wine has no fruit lot. FruitLotID is NULL.
INSERT INTO WineLots (LotCode, FruitLotID, Vintage, Variety, VolumeGal, Status)
VALUES
('19-CH-BULK01', NULL, 2019, N'Chardonnay', 4200.0, 'Blended'),
('19-CS-BULK02', NULL, 2019, N'Cabernet Sauvignon', 6300.0, 'Blended'),
('20-PN-BULK01', NULL, 2020, N'Pinot Noir', 2800.0, 'Blended'),
('20-ZN-BULK02', NULL, 2020, N'Zinfandel', 5100.0, 'Sold'),
('21-CH-BULK01', NULL, 2021, N'Chardonnay', 3900.0, 'Blended'),
('21-CS-BULK02', NULL, 2021, N'Cabernet Sauvignon', 7200.0, 'Blended'),
('21-SY-BULK03', NULL, 2021, N'Syrah', 1900.0, 'Dumped'),
('22-PN-BULK01', NULL, 2022, N'Pinot Noir', 3400.0, 'Blended'),
('22-CH-BULK02', NULL, 2022, N'Chardonnay', 5600.0, 'Sold'),
('22-ME-BULK03', NULL, 2022, N'Merlot', 4100.0, 'Blended'),
('23-CH-BULK01', NULL, 2023, N'Chardonnay', 6800.0, 'Aging'),
('23-CS-BULK02', NULL, 2023, N'Cabernet Sauvignon', 5200.0, 'Aging'),
('23-PN-BULK03', NULL, 2023, N'Pinot Noir', 2600.0, 'Aging'),
('24-SB-BULK01', NULL, 2024, N'Sauvignon Blanc', 4400.0, 'Aging'),
('24-CH-BULK02', NULL, 2024, N'Chardonnay', 7100.0, 'Aging');
GO
-- Deliberate wrinkle: eight deliveries were sold as fruit and never became a
-- wine lot. Inserted after WineLots on purpose, so the corrected anti-join in
-- post 4 (NOT EXISTS) finds exactly these. The two 2023-09-21 rows also
-- guarantee a tied PickDate for post 7's RANGE-vs-ROWS frame demo.
INSERT INTO FruitLots (BlockID, Vintage, PickDate, Tons, Brix, pH)
SELECT b.BlockID, d.Vintage, d.PickDate, d.Tons, d.Brix, d.pH
FROM (VALUES
('KL-01', 2019, '2019-09-24', 3.40, 24.1, 3.61),
('OL-01', 2020, '2020-09-08', 4.10, 22.9, 3.42),
('KL-05', 2021, '2021-09-15', 2.80, 23.5, 3.55),
('DB-02', 2022, '2022-09-12', 3.10, 25.0, 3.70),
('KL-02', 2022, '2022-10-01', 2.20, 24.6, 3.66),
('SC-03', 2023, '2023-09-21', 3.70, 23.8, 3.58),
('KL-03', 2023, '2023-09-21', 4.60, 23.2, 3.51),
('OL-04', 2024, '2024-09-10', 3.00, 21.9, 3.31)
) d (BlockCode, Vintage, PickDate, Tons, Brix, pH)
JOIN VineyardBlocks b ON b.BlockCode = d.BlockCode;
GO
-- ---------------------------------------------------------------------------
-- Blends: ten programs per vintage. 2023+ not yet bottled.
-- ---------------------------------------------------------------------------
INSERT INTO Blends (BlendCode, BlendName, Vintage, TargetVolumeGal, BottlingDate)
SELECT
CONCAT('BL-', v.value, '-', RIGHT('0' + CAST(s.value AS VARCHAR(2)), 2)),
CASE s.value
WHEN 1 THEN N'Estate Pinot Noir'
WHEN 2 THEN N'Russian River Chardonnay'
WHEN 3 THEN N'Sonoma Coast Pinot Noir'
WHEN 4 THEN N'Alexander Valley Cabernet'
WHEN 5 THEN N'Old Vine Zinfandel'
WHEN 6 THEN N'Proprietor Red'
WHEN 7 THEN N'Dry Creek Petite Sirah'
WHEN 8 THEN N'Coastal Syrah'
WHEN 9 THEN N'Clarksburg Sauvignon Blanc'
ELSE N'Winemaker Reserve'
END,
v.value,
CAST(1800 + ABS(CHECKSUM('tgt', v.value, s.value)) % 9200 AS DECIMAL(8,1)),
CASE WHEN v.value >= 2023 THEN NULL
ELSE DATEADD(DAY, ABS(CHECKSUM('bottle', v.value, s.value)) % 120,
DATEFROMPARTS(v.value + 2, 2, 1))
END
FROM GENERATE_SERIES(2019, 2024) v
CROSS JOIN GENERATE_SERIES(1, 10) s
ORDER BY v.value, s.value;
GO
-- Deliberate wrinkle: a second 2023 blend targets exactly 1,200.0 gallons,
-- the same round number as the field blend planted further down, so
-- SUM(DISTINCT TargetVolumeGal) silently collapses the two (post 5). Pinned
-- before components are generated so component volumes stay consistent.
UPDATE Blends SET TargetVolumeGal = 1200.0 WHERE BlendCode = 'BL-2023-05';
GO
-- ---------------------------------------------------------------------------
-- BlendComponents: each blend draws four or five same-vintage lots (about
-- 4.7 on average, which is post 5's fan-out inflation factor).
-- ---------------------------------------------------------------------------
WITH Lots AS (
SELECT WineLotID, Vintage,
ROW_NUMBER() OVER (PARTITION BY Vintage ORDER BY WineLotID) AS LotSeq,
COUNT(*) OVER (PARTITION BY Vintage) AS LotsInVintage
FROM WineLots
),
B AS (
SELECT BlendID, BlendCode, Vintage, TargetVolumeGal,
ROW_NUMBER() OVER (PARTITION BY Vintage ORDER BY BlendID) AS BlendSeq,
CASE WHEN ABS(CHECKSUM('ncomp', BlendCode)) % 3 = 0 THEN 4 ELSE 5 END AS ComponentCount
FROM Blends
)
INSERT INTO BlendComponents (BlendID, WineLotID, ComponentPct, VolumeUsedGal)
SELECT b.BlendID, l.WineLotID,
CAST(100.0 / b.ComponentCount AS DECIMAL(5,2)),
CAST(b.TargetVolumeGal / b.ComponentCount AS DECIMAL(8,1))
FROM B b
CROSS APPLY GENERATE_SERIES(1, b.ComponentCount) s
JOIN Lots l
ON l.Vintage = b.Vintage
AND l.LotSeq = 1 + (b.BlendSeq * 4 + s.value - 1) % l.LotsInVintage;
GO
-- Deliberate wrinkle: two trial components were logged without a percentage.
UPDATE bc
SET ComponentPct = NULL
FROM BlendComponents bc
JOIN Blends b ON b.BlendID = bc.BlendID
WHERE b.BlendCode IN ('BL-2024-02', 'BL-2024-07')
AND bc.WineLotID = (SELECT MIN(x.WineLotID)
FROM BlendComponents x
WHERE x.BlendID = bc.BlendID);
GO
-- The other components of those two blends carry their true shares, which
-- total 92: the unassigned trial component is the missing 8 percent. So
-- SUM(ComponentPct) comes back around 92 and looks like a math error that
-- isn't (post 4).
UPDATE bc
SET ComponentPct = CAST(92.0 / n.NamedComponents AS DECIMAL(5,2))
FROM BlendComponents bc
JOIN Blends b ON b.BlendID = bc.BlendID
CROSS APPLY (SELECT COUNT(*) AS NamedComponents
FROM BlendComponents x
WHERE x.BlendID = bc.BlendID
AND x.ComponentPct IS NOT NULL) n
WHERE b.BlendCode IN ('BL-2024-02', 'BL-2024-07')
AND bc.ComponentPct IS NOT NULL;
GO
-- Plant: post 5's worked example. '23-GSM-01' targets 1,200.0 gallons and
-- draws exactly five lots from the library, so the Blends-to-components join
-- shows five copies of 1200.0 and a naive SUM reports 6,000 gallons of a
-- 1,200-gallon blend.
INSERT INTO Blends (BlendCode, BlendName, Vintage, TargetVolumeGal, BottlingDate)
VALUES ('23-GSM-01', N'GSM Field Blend', 2023, 1200.0, NULL);
INSERT INTO BlendComponents (BlendID, WineLotID, ComponentPct, VolumeUsedGal)
SELECT b.BlendID, w.WineLotID, 20.00, 240.0
FROM Blends b
CROSS JOIN (VALUES (118), (124), (131), (142), (156)) w (WineLotID)
WHERE b.BlendCode = '23-GSM-01';
GO
-- ---------------------------------------------------------------------------
-- Barrels: 900 barrels, seven coopers.
-- ---------------------------------------------------------------------------
INSERT INTO Barrels (BarrelCode, Cooper, Origin, ToastLevel, CapacityGal, FirstFillYear)
SELECT
CONCAT('BRL-', RIGHT('000' + CAST(n.value AS VARCHAR(4)), 4)),
CASE ABS(CHECKSUM('cooper', n.value)) % 7
WHEN 0 THEN N'Francois Freres' WHEN 1 THEN N'Taransaud'
WHEN 2 THEN N'Seguin Moreau' WHEN 3 THEN N'Demptos'
WHEN 4 THEN N'Canton' WHEN 5 THEN N'World Cooperage'
ELSE N'Kadar'
END,
CASE WHEN ABS(CHECKSUM('origin', n.value)) % 19 = 0 THEN NULL
WHEN ABS(CHECKSUM('cooper', n.value)) % 7 <= 3 THEN N'French'
WHEN ABS(CHECKSUM('cooper', n.value)) % 7 <= 5 THEN N'American'
ELSE N'Hungarian'
END,
CASE ABS(CHECKSUM('toast', n.value)) % 9
WHEN 0 THEN NULL
WHEN 1 THEN 'Light' WHEN 2 THEN 'Light'
WHEN 3 THEN 'Med' WHEN 4 THEN 'Med' WHEN 5 THEN 'Med'
WHEN 6 THEN 'Med+' WHEN 7 THEN 'Med+'
ELSE 'Heavy'
END,
CASE WHEN ABS(CHECKSUM('cap', n.value)) % 12 = 0 THEN 79.2 ELSE 59.4 END,
2016 + ABS(CHECKSUM('ffy', n.value)) % 9
FROM GENERATE_SERIES(1, 900) n;
GO
-- ---------------------------------------------------------------------------
-- BarrelFills, part 1: the historical program, vintages 2019-2022. Estate
-- lots only (purchased bulk stays in stainless; Sauvignon Blanc never leaves
-- the tank). The cellar samples each lot into up to seven barrels and the
-- remainder stays in tank. Every one of these fills is closed (EmptyDate
-- set), so post 9's open-fill tie-out never touches them.
-- ---------------------------------------------------------------------------
WITH LotsToBarrel AS (
SELECT wl.WineLotID, wl.Vintage,
ROW_NUMBER() OVER (ORDER BY wl.WineLotID) AS LotSeq,
CAST(LEAST(CEILING(wl.VolumeGal / 59.4), 7) AS INT) AS BarrelCount
FROM WineLots wl
WHERE wl.Vintage <= 2022
AND wl.Variety <> N'Sauvignon Blanc'
AND wl.FruitLotID IS NOT NULL
)
INSERT INTO BarrelFills (BarrelID, WineLotID, FillDate, EmptyDate, FillVolumeGal)
SELECT
1 + (l.LotSeq * 37 + s.value * 11) % 900,
l.WineLotID,
DATEADD(DAY, ABS(CHECKSUM('fill', l.WineLotID, s.value)) % 25,
DATEFROMPARTS(l.Vintage, 10, 20)),
DATEADD(MONTH, 9 + ABS(CHECKSUM('mo', l.WineLotID, s.value)) % 8,
DATEADD(DAY, ABS(CHECKSUM('fill', l.WineLotID, s.value)) % 25,
DATEFROMPARTS(l.Vintage, 10, 20))),
CAST(56.5 + ABS(CHECKSUM('fv', l.WineLotID, s.value)) % 30 / 10.0 AS DECIMAL(6,1))
FROM LotsToBarrel l
CROSS APPLY GENERATE_SERIES(1, l.BarrelCount) s;
GO
-- ---------------------------------------------------------------------------
-- BarrelFills, part 2: the 2023 reserve program. Eight lots are FULLY in
-- barrel: whole barrels at exactly 59.4 gallons each, the remainder in one
-- last barrel, so each lot's open fills sum exactly to WineLots.VolumeGal.
-- Post 9's volume tie-out depends on that. Each lot was barreled in November
-- 2023 and racked to fresh barrels in March 2024, so it carries both closed
-- fills (the November round) and open ones (the March round, EmptyDate NULL).
-- Every other 2023 and all 2024 lots are still in tank, with no fills at all.
-- ---------------------------------------------------------------------------
WITH ProgramLots AS (
SELECT wl.WineLotID, wl.VolumeGal,
ROW_NUMBER() OVER (ORDER BY wl.LotCode) AS LotSeq,
CAST(CEILING(wl.VolumeGal / 59.4) AS INT) AS BarrelCount
FROM WineLots wl
WHERE wl.LotCode IN ('23-PN-HR01', '23-PN-HR07', '23-PN-ET01', '23-PN-RB03',
'23-CH-HR05', '23-CH-RB01', '23-CS-SC01', '23-ZN-DB01')
),
Fills AS (
SELECT p.WineLotID, p.LotSeq, s.value AS BarrelSeq, r.value AS FillRound,
CASE WHEN s.value < p.BarrelCount THEN CAST(59.4 AS DECIMAL(6,1))
ELSE CAST(p.VolumeGal - 59.4 * (p.BarrelCount - 1) AS DECIMAL(6,1))
END AS FillVolumeGal
FROM ProgramLots p
CROSS APPLY GENERATE_SERIES(1, p.BarrelCount) s
CROSS JOIN GENERATE_SERIES(1, 2) r
)
INSERT INTO BarrelFills (BarrelID, WineLotID, FillDate, EmptyDate, FillVolumeGal)
SELECT
CASE WHEN f.FillRound = 1 THEN 1 + (f.LotSeq * 101 + f.BarrelSeq * 3) % 900
ELSE 1 + (f.LotSeq * 113 + f.BarrelSeq * 7) % 900
END,
f.WineLotID,
CASE WHEN f.FillRound = 1 THEN DATEFROMPARTS(2023, 11, 4 + f.LotSeq)
ELSE DATEFROMPARTS(2024, 3, 8 + f.LotSeq)
END,
CASE WHEN f.FillRound = 1 THEN DATEFROMPARTS(2024, 3, 8 + f.LotSeq)
ELSE NULL
END,
f.FillVolumeGal
FROM Fills f;
GO
-- Deliberate wrinkle: one fill event double-entered a day apart. Same barrel,
-- same lot, same fill date; different surrogate key. The primary key cannot
-- see it. The copied row is an open, exactly-59.4-gallon fill on an aging
-- reserve lot, so the grain check (post 6), the ROW_NUMBER dedupe (post 7),
-- and the volume tie-out (post 9) all surface the same 59.4 phantom gallons.
INSERT INTO BarrelFills (BarrelID, WineLotID, FillDate, EmptyDate, FillVolumeGal)
SELECT TOP (1) bf.BarrelID, bf.WineLotID, bf.FillDate, bf.EmptyDate, bf.FillVolumeGal
FROM BarrelFills bf
JOIN WineLots wl ON wl.WineLotID = bf.WineLotID
WHERE wl.LotCode = '23-PN-ET01'
AND bf.EmptyDate IS NULL
AND bf.FillVolumeGal = 59.4
ORDER BY bf.BarrelID;
GO
-- ---------------------------------------------------------------------------
-- WorkOrders: 10-27 operations per lot, trimmed to the present.
-- ---------------------------------------------------------------------------
WITH L AS (
SELECT WineLotID, LotCode, Vintage,
10 + ABS(CHECKSUM('nwo', LotCode)) % 18 AS OpCount
FROM WineLots
)
INSERT INTO WorkOrders (WineLotID, WorkType, ScheduledDate, CompletedDate, CellarHand, LaborHours)
SELECT
l.WineLotID,
CASE ABS(CHECKSUM('wt', l.LotCode, s.value)) % 10
WHEN 0 THEN 'Racking' WHEN 1 THEN 'Racking'
WHEN 2 THEN 'SO2Add' WHEN 3 THEN 'SO2Add'
WHEN 4 THEN 'Topping' WHEN 5 THEN 'Topping' WHEN 6 THEN 'Topping'
WHEN 7 THEN 'PumpOver'
WHEN 8 THEN 'Filtration'
ELSE 'Transfer'
END,
d.ScheduledDate,
CASE WHEN ABS(CHECKSUM('open', l.LotCode, s.value)) % 23 = 0 THEN NULL
ELSE DATEADD(DAY, ABS(CHECKSUM('lag', l.LotCode, s.value)) % 3, d.ScheduledDate)
END,
-- Deliberate wrinkle: some work orders were logged with no cellar hand.
CASE ABS(CHECKSUM('hand', l.LotCode, s.value)) % 8
WHEN 0 THEN N'Ramos' WHEN 1 THEN N'Ramos'
WHEN 2 THEN N'Delgado' WHEN 3 THEN N'Okafor'
WHEN 4 THEN N'Fitch' WHEN 5 THEN N'Nguyen'
WHEN 6 THEN N'Barnes'
ELSE NULL
END,
CASE WHEN ABS(CHECKSUM('hrs', l.LotCode, s.value)) % 19 = 0 THEN NULL
ELSE CAST(0.5 + ABS(CHECKSUM('hrs', l.LotCode, s.value)) % 70 / 10.0 AS DECIMAL(4,1))
END
FROM L l
CROSS APPLY GENERATE_SERIES(1, l.OpCount) s
CROSS APPLY (SELECT DATEADD(DAY,
(s.value - 1) * 17 + ABS(CHECKSUM('sched', l.LotCode, s.value)) % 11,
DATEFROMPARTS(l.Vintage, 9, 20)) AS ScheduledDate) d
WHERE d.ScheduledDate <= '2024-09-30';
GO
-- ---------------------------------------------------------------------------
-- Customers: 25 bulk-wine buyers.
-- ---------------------------------------------------------------------------
INSERT INTO Customers (CustomerName, State, CustomerType)
VALUES
(N'Meridian Cellars', 'CA', 'Winery'),
(N'Halcyon Wine Co.', 'CA', 'Winery'),
(N'Black Oak Vintners', 'CA', 'Winery'),
(N'Sierra Foothill Wineworks', 'CA', 'Winery'),
(N'Cascadia Cellars', 'OR', 'Winery'),
(N'Columbia Crest Partners', 'WA', 'Winery'),
(N'Bluestem Winery', 'TX', 'Winery'),
(N'Seneca Lake Cellars', 'NY', 'Winery'),
(N'Pacific Slope Wines', 'CA', 'Winery'),
(N'Dunlap Family Vintners', 'CA', 'Winery'),
(N'Castellan & Sons', 'CA', 'Negociant'),
(N'Vintner''s Exchange', 'CA', 'Negociant'),
(N'Goldfield Wine Trading', 'CA', 'Negociant'),
(N'Areton Brands', 'CA', 'Negociant'),
(N'Crush Coast Selections', 'CA', 'Negociant'),
(N'Hartwell & Mayer', 'NY', 'Negociant'),
(N'Verraison Group', NULL, 'Negociant'),
(N'Old Bridge Wine Co.', 'OR', 'Negociant'),
(N'Loomis Bulk Services', 'CA', 'Broker'),
(N'Central Valley Wine Brokerage', 'CA', 'Broker'),
(N'Pacific Bulk Partners', 'CA', 'Broker'),
(N'Quail Run Brokerage', 'CA', 'Broker'),
(N'Westgate Wine Brokers', NULL, 'Broker'),
(N'Foss Creek Trading', 'CA', 'Broker'),
(N'Amador Wine Exchange', 'CA', 'Broker');
GO
-- ---------------------------------------------------------------------------
-- BulkContracts: 45 contracts, 2019-2024, selling the prior vintage.
-- ---------------------------------------------------------------------------
INSERT INTO BulkContracts (ContractNumber, CustomerID, ContractDate, Variety, Vintage,
VolumeGal, PricePerGal, Status)
SELECT
CONCAT('BC-', 2019 + (n.value - 1) % 6, '-',
RIGHT('00' + CAST((n.value - 1) / 6 + 1 AS VARCHAR(2)), 3)),
1 + ABS(CHECKSUM('cust', n.value)) % 23, -- the last two customers never buy
-- (post 2's LEFT JOIN demo needs them)
DATEADD(DAY, ABS(CHECKSUM('cdate', n.value)) % 235,
DATEFROMPARTS(2019 + (n.value - 1) % 6, 1, 10)),
CASE ABS(CHECKSUM('cvar', n.value)) % 6
WHEN 0 THEN N'Chardonnay' WHEN 1 THEN N'Cabernet Sauvignon'
WHEN 2 THEN N'Pinot Noir' WHEN 3 THEN N'Zinfandel'
WHEN 4 THEN N'Merlot' ELSE N'Sauvignon Blanc'
END,
2019 + (n.value - 1) % 6 - 1,
CAST(2000 + ABS(CHECKSUM('cvol', n.value)) % 26 * 1000 AS DECIMAL(8,1)),
CAST(4.25 + ABS(CHECKSUM('cprice', n.value)) % 90 / 10.0 AS DECIMAL(8,2)),
CASE WHEN 2019 + (n.value - 1) % 6 = 2024 THEN 'Open'
WHEN ABS(CHECKSUM('cstat', n.value)) % 11 = 0 THEN 'Cancelled'
ELSE 'Fulfilled'
END
FROM GENERATE_SERIES(1, 45) n
ORDER BY n.value;
GO
-- Deliberate wrinkle: BC-2024-007 is the contract the series keeps returning
-- to. Pin its terms exactly.
UPDATE BulkContracts
SET CustomerID = (SELECT CustomerID FROM Customers WHERE CustomerName = N'Meridian Cellars'),
ContractDate = '2024-01-18',
Variety = N'Chardonnay',
Vintage = 2023,
VolumeGal = 12000.0,
PricePerGal = 8.50,
Status = 'Open'
WHERE ContractNumber = 'BC-2024-007';
GO
-- ---------------------------------------------------------------------------
-- ContractAmendments: roughly 40% of contracts get amended; a few twice.
-- ---------------------------------------------------------------------------
INSERT INTO ContractAmendments (ContractID, AmendmentDate, NewVolumeGal, NewPricePerGal, Reason)
SELECT
c.ContractID,
ad.AmendmentDate,
CASE WHEN ABS(CHECKSUM('which', c.ContractNumber, a.value)) % 2 = 0
THEN CAST(c.VolumeGal * (0.7 + ABS(CHECKSUM('vchg', c.ContractNumber, a.value)) % 7 / 10.0) AS DECIMAL(8,1))
ELSE NULL
END,
CASE WHEN ABS(CHECKSUM('which', c.ContractNumber, a.value)) % 2 = 1
THEN CAST(c.PricePerGal * (0.85 + ABS(CHECKSUM('pchg', c.ContractNumber, a.value)) % 4 / 10.0) AS DECIMAL(8,2))
ELSE NULL
END,
CASE ABS(CHECKSUM('why', c.ContractNumber, a.value)) % 4
WHEN 0 THEN N'Volume revised at buyer request'
WHEN 1 THEN N'Price renegotiated on market movement'
WHEN 2 THEN N'Schedule and volume rebalanced'
ELSE NULL
END
FROM BulkContracts c
CROSS APPLY GENERATE_SERIES(1,
CASE WHEN ABS(CHECKSUM('namd', c.ContractNumber)) % 5 = 0 THEN 2 ELSE 1 END) a
CROSS APPLY (SELECT DATEADD(DAY,
45 + ABS(CHECKSUM('amd', c.ContractNumber, a.value)) % 200,
c.ContractDate) AS AmendmentDate) ad
WHERE c.ContractNumber <> 'BC-2024-007'
AND ABS(CHECKSUM('namd', c.ContractNumber)) % 5 <= 1
AND ad.AmendmentDate <= '2024-09-30';
GO
-- Deliberate wrinkle: BC-2024-007 amended twice. Price reduced in March,
-- volume increased in June. The contract row above still holds the original
-- terms; current terms exist only by reading the amendments.
INSERT INTO ContractAmendments (ContractID, AmendmentDate, NewVolumeGal, NewPricePerGal, Reason)
SELECT ContractID, '2024-03-14', NULL, 7.90, N'Price adjusted to bulk market'
FROM BulkContracts
WHERE ContractNumber = 'BC-2024-007';
INSERT INTO ContractAmendments (ContractID, AmendmentDate, NewVolumeGal, NewPricePerGal, Reason)
SELECT ContractID, '2024-06-20', 15000.0, NULL, N'Volume increased at buyer request'
FROM BulkContracts
WHERE ContractNumber = 'BC-2024-007';
GO
-- ---------------------------------------------------------------------------
-- Shipments: 1-4 shipments per non-cancelled contract, trimmed to the present.
-- ---------------------------------------------------------------------------
INSERT INTO Shipments (ContractID, ShipDate, VolumeGal, CarrierRef)
SELECT
c.ContractID,
sd.ShipDate,
CAST(c.VolumeGal / (1 + ABS(CHECKSUM('nship', c.ContractNumber)) % 4) AS DECIMAL(8,1)),
CASE WHEN ABS(CHECKSUM('pro', c.ContractNumber, s.value)) % 7 = 0 THEN NULL
ELSE CONCAT('PRO-', RIGHT('00000' +
CAST(ABS(CHECKSUM('pro', c.ContractNumber, s.value)) % 100000 AS VARCHAR(6)), 6))
END
FROM BulkContracts c
CROSS APPLY GENERATE_SERIES(1, 1 + ABS(CHECKSUM('nship', c.ContractNumber)) % 4) s
CROSS APPLY (SELECT DATEADD(DAY,
s.value * 40 + ABS(CHECKSUM('sd', c.ContractNumber, s.value)) % 25,
c.ContractDate) AS ShipDate) sd
WHERE c.Status <> 'Cancelled'
AND c.ContractNumber <> 'BC-2024-007'
AND sd.ShipDate <= '2024-09-30';
GO
-- Deliberate wrinkle: BC-2024-007 ships against its amended terms. Three
-- shipments of 5,000 gallons each, 15,000 in total: that ties to the amended
-- volume, not the original 12,000 (posts 5, 6, and 8 all lean on this).
INSERT INTO Shipments (ContractID, ShipDate, VolumeGal, CarrierRef)
SELECT c.ContractID, d.ShipDate, d.VolumeGal, d.CarrierRef
FROM BulkContracts c
CROSS APPLY (VALUES
(CAST('2024-04-02' AS DATE), CAST(5000.0 AS DECIMAL(8,1)), 'PRO-118204'),
(CAST('2024-07-11' AS DATE), CAST(5000.0 AS DECIMAL(8,1)), 'PRO-121873'),
(CAST('2024-09-05' AS DATE), CAST(5000.0 AS DECIMAL(8,1)), 'PRO-124551')
) d (ShipDate, VolumeGal, CarrierRef)
WHERE c.ContractNumber = 'BC-2024-007';
GO
-- ---------------------------------------------------------------------------
-- Indexes the later posts lean on.
-- ---------------------------------------------------------------------------
CREATE INDEX IX_FruitLots_BlockID ON FruitLots (BlockID);
CREATE INDEX IX_WineLots_FruitLotID ON WineLots (FruitLotID);
CREATE INDEX IX_BlendComponents_WineLot ON BlendComponents (WineLotID);
CREATE INDEX IX_BarrelFills_WineLotID ON BarrelFills (WineLotID);
CREATE INDEX IX_BarrelFills_BarrelID ON BarrelFills (BarrelID);
CREATE INDEX IX_WorkOrders_WineLotID ON WorkOrders (WineLotID);
-- No index on WorkOrders.ScheduledDate on purpose: the performance post has
-- you create IX_WorkOrders_ScheduledDate yourself and watch the plan change.
CREATE INDEX IX_Shipments_ContractID ON Shipments (ContractID);
GO
PRINT 'CellarDB setup complete.';
GOCheck your install
Run this after the script finishes:
SELECT t.name AS TableName, SUM(p.rows) AS RowCnt
FROM sys.tables t
JOIN sys.partitions p
ON p.object_id = t.object_id AND p.index_id IN (0, 1)
GROUP BY t.name
ORDER BY t.name;Twelve tables, with BarrelFills and WorkOrders carrying most of the rows. If that's what you see, you have everything the series needs. Start with SQL in an Afternoon.
— Lo