I’m having an issue with an SQL query. Below are the tables and fields of importance.
I have the following query:
The problem is that if there is more than one entry in the Orderdetails table for a certain order (meaning that one order consists of more than one course), my amounts (TotalCourseAmt, promodiscount, tierdiscount…) get counted more than once. This obviously skews the results (Gross amount is more than twice what it should be, as are all other amounts) – is there any way to prevent this?
SUM(c2.totalcourseamt) AS [Gross],
SUM(c2.promodiscount) AS [PROMO DISCOUNT],
SUM(c2.tierdiscount) AS [TIER DISCOUNT],
SUM(c2.affiliatediscount) AS [AFFILIATE DISCOUNT],
SUM(c2.addldiscount) AS [ADDL DISCOUNT],
SUM(c2.shippingcharge) AS [SHIPPING],
SUM(c2.expressdeliverycharge) AS [EXPRESS DELIVERY],
SUM(c2.totalcharge) AS [Total_Charge]
FROM orderdetails c1, orders c2, coursecatalog c3
AND c2.orderdate>='20060801' AND c2.orderdate<='20060831'
GROUP BY c1.coursekey, c3.coursename, c3.statecd;
Not really. The problem is that you are trying to aggregate dollars stored at the order (c2) level, but you want them broken out at the order detail (c1) level of granularity. You cant do that.
What is the business user trying to do/accomplish?
Are you sure the dups are coming from the ordedetails join? I just noticed, there are columns from two different tables (c1, c3) in the GROUP BY.
You might try removing the c3 columns and c3 join from the query just to be sure it's not that join causing the dups.
If I knew more about what the biz user wanted to accomplish, I could suggest some other ideas. This isnt an uncommon issue.
Also, usually, the ordedetails table contains the item level price, qty and sometimes the extended $. If so, some other works arounds may get you close to what you are trying to accomplish. Please advise.
Our CFO wants a report that details, by month and by state, the total sales per course (gross), the discounts given on those courses (promo, tier, affiliate, additional discounts), the extra charges (shipping, express delivery), and the net sales per course. So, he'd like to know that, for example, in January of 2006, we had gross sales of $5,000 for course A, we gave $1,000 in discounts on that course, charged $500 in extra fees, for net sales of $4,500. The main problem I'm having is that the discounts and the extra charges are in the orders table (because a discount and an extra charge is applied to an order as whole, and not to the individual courses within that order).
All the prices are in the orders table - the orderdetails table doesn't have any pricing information other than the regular course price (which is useless).
That's a tough one. The DB doesnt track the transactions at the level of detail that the CFO wants. You wont be able to meet his requirements with the DB as it currently exists.
I would first explain to the CFO why the data isnt available and give him the option of paying to modify the app/db (if possible) so that, going forward, he can have the level of detail he wants. That said, I would bet it isnt cost effective to make the required changes, but I could be wrong.
Next I would offer some other reports that would help him to better understand the sales given the available data...
1. count of sales by month by course by state. Multiple count by retail price to show what gross would be without discounts.
2. build a frequency distribution of # of courses per order so he can see the factor of courses per order.
3. build a temp table at the orderdetails level of granularity so that I could show average net sales by course by state. I would calculate the average discounts and the average extra charges as defined above for each orderdetail key and subtract it from the "gross" as I mentioned above. I would put these averages in a temp table and build reports off of this showing gross-to-net. It wont be precise, but it will be a fair representation of the data.
I have more ideas, but a phone call would be more efficient.
This is the kinda stuff that we do at my firm. I would be happy to discuss offline. Pro bono of course. PM if you want to discuss further.
Wouldnt Crystal Reports help in this...using a sub report to calculate the prices and discounts???
and the the main report to sort it all out?
Thats what we use for this type of reporting
Unfortunately, no. The problem isnt the toolset. It's a DB design issue. The dollars are not stored at the lower (course) level of detail. They are stored at a higher (order) level. Hence, you cant aggregate (in any tool) the order dollars at any level lower than the order level.
Yes, CSR is correct, and I appreciate the feedback :) I'm going to have a sit-down with the CFO and explain the situation. I'll give him some reports that we can pull without having to manually comb through all data - maybe he'll see something he likes there :)
I've done some crazy **** before so here's an idea
grab the orderID and and count the order detail entries. If more than one entry dump the rows into a temp table.
Script your view
same as above, however the script will compare orderID's/ If exist in temp table, don't add to view.
You should then be able to report on the view that gives all assoiciated order details.
Thanks, dino! For now, our CFO is settling for an overview per month per industry - those are easy queries :D
I might give your suggestion a try, though - sounds like a plan to me :)