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.
1条答案
按热度按时间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 aWHERE
db<>fiddle
The reason for you using
FULL
andLEFT
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.