If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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?

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.

Last edited by Cheap Scotch Ron; March 23rd, 2009 at 11:16 PM.
Reason: more thoughts

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.

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