SQL Query Help, Please

I can't get 100% there because I have a divide by zero error for the profit margin, but I can at least get you total profit and you can calculate the margin manually (if you call Excel manual).

------------ SELECT PUBLIC_TransactionEntry.StoreID, Store.Name, PUBLIC_TransactionEntry.DiscountReasonCodeID, ReasonCode.Description, Sum((Price)*quantity) AS 'Total Sales', Sum(Cost*quantity) AS 'Total Cost', Sum((Price-Cost)*quantity) AS 'Profit' FROM PUBLIC_Transaction PUBLIC_Transaction, PUBLIC_TransactionEntry PUBLIC_TransactionEntry, ReasonCode ReasonCode, Store Store WHERE PUBLIC_TransactionEntry.DiscountReasonCodeID = ReasonCode.ID AND PUBLIC_TransactionEntry.StoreID = Store.ID AND PUBLIC_Transaction.TransactionNumber PUBLIC_TransactionEntry.TransactionNumber AND (PUBLIC_Transaction.Time Between '2008-01-01' And '2008-05-31') GROUP BY PUBLIC_TransactionEntry.StoreID, Store.Name, PUBLIC_TransactionEntry.DiscountReasonCodeID, ReasonCode.Description HAVING (PUBLIC_TransactionEntry.DiscountReasonCodeID)

------------

This will give you StoreID, Store Name, Total Sales, Total Cost, and Total Profit

WARNING: If you are on a VAT system, the TransactionEntry table stores the sold price including VAT, so you need to subtract the sales tax column. Note that the SalesTax field is the total sales tax for the line item, not the sales tax per unit, so you subtract sales tax after the price*quantity operation. You would use this query:

------------ SELECT PUBLIC_TransactionEntry.StoreID, Store.Name, PUBLIC_TransactionEntry.DiscountReasonCodeID, ReasonCode.Description, Sum((Price)*quantity-SalesTax) AS 'Total Sales', Sum(Cost*quantity) AS 'Total Cost', Sum((Price-Cost)*quantity-SalesTax) AS 'Profit' FROM PUBLIC_Transaction PUBLIC_Transaction, PUBLIC_TransactionEntry PUBLIC_TransactionEntry, ReasonCode ReasonCode, Store Store WHERE PUBLIC_TransactionEntry.DiscountReasonCodeID = ReasonCode.ID AND PUBLIC_TransactionEntry.StoreID = Store.ID AND PUBLIC_Transaction.TransactionNumber PUBLIC_TransactionEntry.TransactionNumber AND (PUBLIC_Transaction.Time Between '2008-01-01' And '2008-05-31') GROUP BY PUBLIC_TransactionEntry.StoreID, Store.Name, PUBLIC_TransactionEntry.DiscountReasonCodeID, ReasonCode.Description HAVING (PUBLIC_TransactionEntry.DiscountReasonCodeID)

------------

Let me know if this works for you. I'd be happy to adjust it if you need to tweak it.

Jason

Jason, THANK YOU very much. I am getting a 'strange' dollar amount for the total sales. As you can see, my total sales were $160 but my cog was $2538.22. This is obviously wrong.

5300 Coconut Point 126 WhackFree itm of ur choice w/any prchs-retail only 160 2538.22 -2378.22

Just to clarify ... I'd like to know the total sales for all transactions in which one or more items in the transaction had a particular discount code applied. So, if the transaction had three items and one item was discounted the total sale would be the sum of the price; the total cog would be the cost of goods for EACH of the three items added together and the margin (profit would be good enough) is the total price paid for the three items minus the cog for the three items.

"Jas> I can't get 100% there because I have a divide by zero error for the profit

That is going to be pretty hard to figure out with a single SQL query I think because each line item in the database has a discount reason code, not each tranasaction.

To answer your question about the strange dollar amount, the query should work. Maybe you have a bad cost in the database that is skewing the results. What I would do it remove the SUM() functions for total sales, total cost, and profit. This will give you every line item where the discount code is X. You can trace the problem down to the offending line item. Again - Excel is great because you can sort by the profit and see if you have any big negative amounts.

To accomplish what you really need with SQL, you would need to set up another table I think. First you have to determine what transactions contained a discounted item. Then you need to figure out the totals for those transactions. You could pull a query into Excel with all line items, and sort it all out with formulas, but that is not fun, either.

Let me think about that...

Coconut Point, huh? Estero FL? I live about 15 minutes from there.

Yes, I believe that we've previously "spoken" about Estero. If you can figure out a way to get the numbers I need I would appreciate it. I'm usually good with SQL but I'm drawing a blank when it comes to this one.

Scott

"Jas> That is going to be pretty hard to figure out with a single SQL query I

Yes, that's exactly what I finally concluded. I've got it working - if anyone else is interested in it let me know.

Scott

"c> Luminox - you may be able to get this done with a nested query (two select

Scott,

Glad you got it working. I'd love to see the solution.

Didn't Luminox = Scott.

:)

Jason

I still am having a problem with SUMMARIZING the report by store. I will contact you OFFLINE if I can dig out your e-mail address.

Scott

"Jas> Scott,

One important note... Actually to get June results you want to use:

[Transaction].Time BETWEEN '2008-06-01' AND '2008-07-01'

If you use :

[Transaction].Time BETWEEN '2008-06-01' AND '2008-06-30'

... you will only get results through June 29 because SQL reads 2008-06-30 as midnight on the 30th. You won't get any sales after the stroke of midnight on the 30th.

I'm interested in seeing your SQL query, can you please post it?

Luminox, I inadvertently replied to the wrong post, but I'm interested in seeing your SQL query, can you please post it?

Thanks!