Sql 无法将发票与产品价格相关联

将发票选项与原material 价格链接(历史表)

我无法在以下查询的倒数第二行使用发票i.DateSent，因为我收到以下错误.另外，如果有更好的方法来设计数据库/表的基础上，请让我知道，或者如果你有任何资源，我应该寻找，使我的概念更强大.谢谢!

错误:

Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "i.DateSent" could not be bound.
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "i.DateSent" could not be bound.

-你在说什么？

SELECT i.InvoiceID, i.DateSent, io.InvoiceOptionID, i.Customer, RawMaterialA.Name AS RawMaterialA, RawMaterialA.Price AS PriceA, RawMaterialB.Name AS RawMaterialB, RawMaterialB.Price AS PriceB
FROM InvoiceOption io
FULL JOIN Invoice i ON io.InvoiceID = i.InvoiceID
LEFT JOIN (
    SELECT TOP 1 rm.RawMaterialID, rm.Name, rmp.Price, rmp.EffectiveDate
    FROM RawMaterial rm
    LEFT JOIN RawMaterialPrice rmp ON rm.RawMaterialID = rmp.RawMaterialID AND i.DateSent > rmp.EffectiveDate
) AS RawMaterialA ON io.RawMaterialAID = RawMaterialA.RawMaterialID
LEFT JOIN (
    SELECT TOP 1 rm.RawMaterialID, rm.Name, rmp.Price, rmp.EffectiveDate
    FROM RawMaterial rm
    LEFT JOIN RawMaterialPrice rmp ON rm.RawMaterialID = rmp.RawMaterialID AND i.DateSent > rmp.EffectiveDate
) AS RawMaterialB ON io.RawMaterialBID = RawMaterialB.RawMaterialID 

我想要的结果

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

上下文: 假设我有一个表发票，它在InvoiceOption表中可以有多个选项.InvoiceOption取决于两种类型的原material A和B.但原material 价格可以改变，旧发票应该能够判断所用原material 的价格.为此，我基于以下条件使用原始material 价格的生效日期和发票的日期发送来联接InvoiceOption和原始material 价格表:(获取第一个价格where Invoice.DateSent&gt；原始material 价格.EffectiveDate).生成数据和脚本的表格如下所示:

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');

我试着询问Bing聊天，并理解我.DateSent可能超出了我创建的连接的范围，但无法生成有效的答案.