SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Invoice (
    InvoiceID INT PRIMARY KEY,
    Customer NVARCHAR(255),
    DateSent DATE,
    DateCreated DATE
);

-- Sample data
INSERT INTO Invoice (InvoiceID, Customer, DateSent, DateCreated)
VALUES
    (1, 'Customer A', '2023-12-01', '2023-11-28');

CREATE TABLE InvoiceOption (
    InvoiceOptionID INT PRIMARY KEY,
    InvoiceID INT,
    RawMaterialAID INT,
    RawMaterialBID INT,
    FOREIGN KEY (InvoiceID) REFERENCES Invoice(InvoiceID)
);

-- Sample data
INSERT INTO InvoiceOption (InvoiceOptionID, InvoiceID, RawMaterialAID, RawMaterialBID)
VALUES
    (101, 1, 101, 102),
    (102, 1, 101, 103);

CREATE TABLE RawMaterial (
    RawMaterialID INT PRIMARY KEY,
    Name NVARCHAR(255),
    Category NVARCHAR(50)
);

-- Sample data
INSERT INTO RawMaterial (RawMaterialID, Name, Category)
VALUES
    (101, 'Material X', 'Metal'),
    (102, 'Material Y', 'Wood'),
    (103, 'Material Z', 'Wood');

CREATE TABLE RawMaterialPrice (
    RawMaterialPriceID INT PRIMARY KEY,
    RawMaterialID INT,
    Price DECIMAL(10, 2),
    EffectiveDate DATE,
    FOREIGN KEY (RawMaterialID) REFERENCES RawMaterial(RawMaterialID)
);

-- Sample data
INSERT INTO RawMaterialPrice (RawMaterialPriceID, RawMaterialID, Price, EffectiveDate)
VALUES
    (1001, 101, 50.00, '2023-11-01'),
    (1002, 102, 30.00, '2023-11-01'),
    (1003, 103, 20.00, '2023-11-01'),
    (1004, 103, 10.00, '2023-10-01');

10 rows affected

InvoiceID	DateSent	InvoiceOptionID	Customer	RawMaterialA	PriceA	RawMaterialB	PriceB
1	2023-12-01	101	Customer A	Material X	50.00	Material Y	30.00
1	2023-12-01	102	Customer A	Material X	50.00	Material Z	20.00