SQL Server Unable to link Invoice with Product Prices

nfzehxib  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(109)

Linking Invoice Options with Raw Material Prices (History table)

I am unable to use the Invoice i.DateSent in the second last line of the following query as I'm getting the following error. Also, if there is a better way to design the database / tables based, please let me know or if you any resources I should look up to make my concepts stronger. Thanks!

Error:

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.

Query:

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

My desired result

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

Context: Suppose I have a table Invoice which can have multiple options in InvoiceOption Table. The InvoiceOption depends on 2 types raw material A and B. But a raw material price can change and an old invoice should be able to check the price of the raw material used for. For this I'm using the EffectiveDate of RawMaterialPrice and DateSent of Invoice to join the InvoiceOption and RawMaterialPrice tables based on the following condition: (get the first price where Invoice.DateSent > RawMaterialPrice.EffectiveDate). The tables to generate the data and script is given below:

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

I tried asking Bing chat and understood i.DateSent might be out of scope in the joins I created but was unable to generate a valid answer.

2lpgd968

2lpgd9681#

Looks like you want an OUTER APPLY as this allows you to use outer references in the derived table. The join condition needs to be pushed inside as a WHERE

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
JOIN Invoice i ON io.InvoiceID = i.InvoiceID
OUTER APPLY (
    SELECT TOP (1)
      rm.RawMaterialID,
      rm.Name,
      rmp.Price,
      rmp.EffectiveDate
    FROM RawMaterial rm
    JOIN RawMaterialPrice rmp ON rm.RawMaterialID = rmp.RawMaterialID
    WHERE i.DateSent > rmp.EffectiveDate
      AND rm.RawMaterialID = io.RawMaterialAID
    ORDER BY rmp.EffectiveDate DESC
) AS RawMaterialA
OUTER APPLY (
    SELECT TOP (1)
      rm.RawMaterialID,
      rm.Name,
      rmp.Price,
      rmp.EffectiveDate
    FROM RawMaterial rm
    JOIN RawMaterialPrice rmp ON rm.RawMaterialID = rmp.RawMaterialID
    WHERE i.DateSent > rmp.EffectiveDate
      AND rm.RawMaterialID = io.RawMaterialBID
    ORDER BY rmp.EffectiveDate DESC
) AS RawMaterialB;

db<>fiddle

The reason for you using FULL and LEFT joins in some cases is not clear. They should probably be inner joins as noted.

I've also added an ORDER BY to make the results deterministic.

相关问题