SQL Query Help, Please

I'd like to run a query that will list all of the transactions during a certain period of time that have a particular discount reason code in them.
The query should SUMMARIZE the store id, store name, total of the sales, the summary of the cost of cogs for each item in the transaction (whether the discount was applied to that item or not), and the summarized profit margin.
Can someone please (he says with a bit of desperation) help?????
Scott
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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.
"Jason" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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.

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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
"Jason" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Luminox - you may be able to get this done with a nested query (two select statements in one) -- I think I wrote a similar query a few months ago, with a little tweaking I may get it to work - I'll repost.
"Luminox" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Yes, that's exactly what I finally concluded. I've got it working - if anyone else is interested in it let me know.
Scott
"convoluted" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Scott,
Glad you got it working. I'd love to see the solution.
Didn't Luminox = Scott.
:)
Jason

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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
"Jason" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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.

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Thanks. Assume you got my e-mail?
"Jason" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
I'm interested in seeing your SQL query, can you please post it?

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Luminox, I inadvertently replied to the wrong post, but I'm interested in seeing your SQL query, can you please post it?
Thanks!

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

BeanSmart.com is a site by and for consumers of financial services and advice. We are not affiliated with any of the banks, financial services or software manufacturers discussed here. All logos and trade names are the property of their respective owners.

Tax and financial advice you come across on this site is freely given by your peers and professionals on their own time and out of the kindness of their hearts. We can guarantee neither accuracy of such advice nor its applicability for your situation. Simply put, you are fully responsible for the results of using information from this site in real life situations.