N
Nick
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<= '#Form.enddate#'.
I get a total sum, but it is not the same total as is showing on the
detailed sales report. Anyone have any help on how to fix this?
Thanks,
nick
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<= '#Form.enddate#'.
I get a total sum, but it is not the same total as is showing on the
detailed sales report. Anyone have any help on how to fix this?
Thanks,
nick