New query! Mountain Design uses a FIFO inventory system and needs to know information about the earliest purchase of a product from the PurchaseHistory table. The purpose of this question is to create a result table containing information about the oldest purchase of a product from the PurchaseHistory table (tblPurchaseHistory). this question asks you to figure out which purchase in the PurchaseHistory table was made from the earliest date (the MIN date). Based on that purchase, we want to know the price that was paid. Think of it that we want to know the oldest price paid, which can only be determined by figuring out the MIN date that a product was purchased in the PurchaseHistory table.

The result table should include all products from the Product table (tblProduct). The data for this query should come only from the PurchaseHistory and Product tables. The data for this query should NOT come from the PurchaseOrder or PurchaseOrderLine tables.

Include the ProductID, ProductDescription, ProductEOQ, the oldest date a product was purchased, the quantity that was purchased on that date, and the purchase price on that date (the last three fields are all from tblPurchaseHistory). Sort the result table by ProductID. Hints: This query must be written using a correlated subquery, view or CTE. A few of the products in the Product table are not in the PurchaseHistory table, and they must be included in the result table. That means you will need to use an OUTER JOIN with the Product table. It also means you will have to check for the oldest date OR whether the date is NULL.

used the ISNULL function to replace the qty and price in the result table with zeros when there was no purchase for a product in tblPurchaseHistory. And left the OldestPurchaseDate as a NULL value when there was not a purchase for the product in the PurchaseHistory table.

The Result table has 6 columns (ProductID, ProductDescription, ProductEconomicOrderQuantity, OldestPurchaseDate, QuantityPurchased, and PurchasePrice).

I came up with the following Code but struggle to get it right. Please send me the accurate SQL Code. Thank you

SELECT ProductID, ProductDescription, ProductEOQ, ISNULL(MIN(DatePurchased), NULL) AS OldestPurchaseDate, ISNULL(SUM(QtyPurchased), 0) AS QtyPurchased, ISNULL(AVG(PricePaid), 0) AS PricePaid FROM tblProduct LEFT JOIN tblPurchaseHistory ON tblProduct.ProductID = tblPurchaseHistory.ProductID GROUP BY ProductID, ProductDescription, ProductEOQ ORDER BY ProductID;