SELECT DISTINCT(od.InvoiceNo), c.Name AS Contact,
c.Street + ' ' + c.City + ' ' + c.Zip AS Address,
c.TIN, od.DateAdded, od.SONo AS PurchaseNo,
odd.Quantity, i.Name, i.Description,
ISNULL((SELECT odt.Price FROM Orders_Details odt
WHERE odt.ItemID = i.ItemID AND odt.OrderNo = od.SONo),
(SELECT TOP 1 Price FROM Items_Prices
WHERE ItemID = i.ItemID ORDER BY RefNo DESC)) AS Price,
(SELECT odt.DollarPrice FROM Orders_Details odt
WHERE odt.ItemID = i.ItemID AND odt.OrderNo = od.SONo) AS DollarPrice,
ISNULL((SELECT odt.Amount FROM Orders_Details odt
WHERE odt.ItemID = i.ItemID AND odt.OrderNo = od.SONo),
(SELECT TOP 1 Price FROM Items_Prices
WHERE ItemID = i.ItemID ORDER BY RefNo DESC) * odd.Quantity) AS Amount
FROM Orders_Deliveries od
LEFT JOIN Orders o ON o.SONo = od.SONo
INNER JOIN Orders_Deliveries_Details odd ON od.DeliveryNo = odd.DeliveryNo
INNER JOIN Customers c ON odd.CustomerID = c.CustomerID
INNER JOIN Items i ON odd.ItemID = i.ItemID
WHERE od.InvoiceNo=23456