Query Help

[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

 

[Nick]

Would someone please give me a hand here? Anyone?

Thanks,
nick

 

[Nick]

Would someone please give me a hand here? Anyone?

Thanks,
nick

 

[Holly C]

I don't see any reference to Quantity in your query. I haven't tested this,
but I believe you need to modify your select statement to accomodate
TransactionEntry.price * TransactionEntry.Quantity.

Hope this helps.

Would someone please give me a hand here? Anyone?

Thanks,
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

 

[Nick]

Holly,

Where would I add this to in my query? I think I know what you mean, I want
to multiply each transaction item by the quantity that was sold, then add
those up at the end to get my final total price. What SQL would I use for
this?

Thanks,

I don't see any reference to Quantity in your query. I haven't tested
this, but I believe you need to modify your select statement to accomodate
TransactionEntry.price * TransactionEntry.Quantity.

Hope this helps.

Would someone please give me a hand here? Anyone?

Thanks,
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

 

[Holly C]

Nick,

It will go right in your select statement. So: SELECT
Sum([TransactionEntry.Price]*[TransactionEntry.Quantity]) AS Total FROM.....
and the remainder as you have it.

Holly

Holly,

Where would I add this to in my query? I think I know what you mean, I
want to multiply each transaction item by the quantity that was sold, then
add those up at the end to get my final total price. What SQL would I use
for this?

Thanks,

I don't see any reference to Quantity in your query. I haven't tested
this, but I believe you need to modify your select statement to accomodate
TransactionEntry.price * TransactionEntry.Quantity.

Hope this helps.

Would someone please give me a hand here? Anyone?

Thanks,
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

 

[Nick]

Thanks Holly,

It is working better, but I still am not coming up with the same total as
the HQ Detailed Sales Report is giving. Very weird....

Nick,

It will go right in your select statement. So: SELECT
Sum([TransactionEntry.Price]*[TransactionEntry.Quantity]) AS Total
FROM..... and the remainder as you have it.

Holly

Holly,

Where would I add this to in my query? I think I know what you mean, I
want to multiply each transaction item by the quantity that was sold,
then add those up at the end to get my final total price. What SQL would
I use for this?

Thanks,

I don't see any reference to Quantity in your query. I haven't tested
this, but I believe you need to modify your select statement to
accomodate TransactionEntry.price * TransactionEntry.Quantity.

Hope this helps.

Would someone please give me a hand here? Anyone?

Thanks,
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

 

[Holly C]

My bet is that it is the date range. Because the database field includes
timestamps, selecting date ranges can be interesting at best.

Thanks Holly,

It is working better, but I still am not coming up with the same total as
the HQ Detailed Sales Report is giving. Very weird....

Nick,

It will go right in your select statement. So: SELECT
Sum([TransactionEntry.Price]*[TransactionEntry.Quantity]) AS Total
FROM..... and the remainder as you have it.

Holly

Holly,

Where would I add this to in my query? I think I know what you mean, I
want to multiply each transaction item by the quantity that was sold,
then add those up at the end to get my final total price. What SQL would
I use for this?

Thanks,
I don't see any reference to Quantity in your query. I haven't tested
this, but I believe you need to modify your select statement to
accomodate TransactionEntry.price * TransactionEntry.Quantity.

Hope this helps.

Would someone please give me a hand here? Anyone?

Thanks,
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