Icon800.268.6649

Order Items Report Query

SELECT Orders.OrderID, OrderItems.OrderItemID, Orders.OrderDate, Orders.ContactID, Contacts.CompanyID, Orders.ShippingName, Orders.ShippingAddress1, Orders.ShippingAddress2, Orders.ShippingCity, Orders.ShippingZip, States.StateCode, Orders.ShippingState, Orders.OrderStatus, Orders.FinancialStatusID, Orders.CreationDate, Orders.LastUpdatedDate, Orders.OrderType, Orders.InvDate, Orders.Active, Orders.TaxTotal, Orders.ShippingTotal, Orders.OrderTotal, Orders.InvTaxTotal, Orders.InvShippingTotal, Orders.InvTotal, Contacts.FirstName, Contacts.LastName, Terms.Description AS TermsDescription, Inventory.ProductCode, Inventory.ProductSubCode, Items.ItemName, OrderItems.QtyOrdered, OrderItems.QtyShipped, OrderItems.UnitPrice, (SELECT Description FROM Statuses WHERE Statuses.SystemID= Orders.OrderStatus And Statuses.StatusTypeID=11) AS OrderStatusDescription, (SELECT Description FROM Statuses WHERE Statuses.SystemID= Orders.FinancialStatusID And Statuses.StatusTypeID=2) AS FinancialStatusDescription, Payments.PaymentStatusID, (SELECT Description FROM Statuses WHERE Statuses.SystemID= Payments.PaymentStatusID And Statuses.StatusTypeID=2) AS PaymentDescription
FROM (OrderItems LEFT JOIN ((((Orders LEFT JOIN Contacts ON Orders.ContactID=Contacts.ContactID) LEFT JOIN Terms ON Orders.TermsID=Terms.TermsID) LEFT JOIN States ON Orders.ShippingState=States.StateID) LEFT JOIN Payments ON Orders.OrderID = Payments.OrderID ) ON OrderItems.OrderID=Orders.OrderID) LEFT JOIN (Inventory LEFT JOIN Items ON Inventory.ItemID=Items.ItemID) ON OrderItems.InventoryID=Inventory.InventoryID ORDER BY OrderItems.OrderID, OrderItems.OrderItemID