Ok, I am trying to write a query that basically calculates the same data as the HQ Report labeled "Detailed Sales Report".
The only number I want is the final total sales run between two dates. This information is going on a custom report I am creating for my CFO.
The query I have so far is this:
SELECT sum(transactionentry.price) AS price FROM TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber AND TransactionEntry.StoreID [Transaction].StoreID INNER JOIN Batch WITH(NOLOCK) ON [Transaction].BatchNumber = Batch.BatchNumber AND [Transaction].StoreID Batch.StoreID LEFT JOIN Item WITH(NOLOCK) ON TransactionEntry.ItemID Item.ID LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID Department.ID LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID Category.ID LEFT JOIN Supplier WITH(NOLOCK) ON Item.SupplierID = Supplier.ID LEFT JOIN ReasonCode AS ReasonCodeDiscount WITH(NOLOCK) ON TransactionEntry.DiscountReasonCodeID = ReasonCodeDiscount.ID LEFT JOIN ReasonCode AS ReasonCodeTaxChange WITH(NOLOCK) ON TransactionEntry.TaxChangeReasonCodeID = ReasonCodeTaxChange.ID LEFT JOIN ReasonCode AS ReasonCodeReturn WITH(NOLOCK) ON TransactionEntry.ReturnReasonCodeID = ReasonCodeReturn.ID LEFT JOIN Store ON [Transaction].StoreID = Store.ID WHERE [Transaction].Time>'#Form.startdate#' and [Transaction].Time