-
March 23rd, 2009, 07:25 PM
#1
SQL Query
I’m having an issue with an SQL query. Below are the tables and fields of importance.
Dbo.ORDERS
OrderID (PK)
Totalcourseamt
Promodiscount
Tierdiscount
Affiliatediscount
Addldiscount
Shippingcharge
Expressdeliverycharge
Totalcharge
Dbo.ORDERDETAILS
Orderdetailskey (PK)
OrderID (FK)
Coursekey (FK)
Dbo.COURSECATALOG
Coursekey (PK)
Coursename
I have the following query:
Code:
SELECT
c3.coursename,
c3.statecd,
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
WHERE c1.orderid=c2.orderid
AND c1.coursekey=c3.coursekey
AND c2.orderdate>='20060801' AND c2.orderdate<='20060831'
GROUP BY c1.coursekey, c3.coursename, c3.statecd;
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?
-
March 23rd, 2009, 09:37 PM
#2
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 10:16 PM.
Reason: more thoughts
In God We Trust....Everything else we backup.
-
March 23rd, 2009, 11:16 PM
#3
Ron,
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).
-
March 24th, 2009, 02:02 AM
#4
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.
ron
In God We Trust....Everything else we backup.
-
March 24th, 2009, 01:37 PM
#5
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
MLF
How people treat you is their karma- how you react is yours-Wayne Dyer
-
March 24th, 2009, 02:35 PM
#6
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.
In God We Trust....Everything else we backup.
-
March 24th, 2009, 04:30 PM
#7
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
-
March 24th, 2009, 07:18 PM
#8
Neg:
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.
follow?
09:F9:11:02:9D:74:E3:5B 8:41:56:C5:63:56:88:C0
-
March 25th, 2009, 03:44 PM
#9
Thanks, dino! For now, our CFO is settling for an overview per month per industry - those are easy queries
I might give your suggestion a try, though - sounds like a plan to me
Similar Threads
-
By mikester2 in forum Other Tutorials Forum
Replies: 5
Last Post: January 31st, 2005, 01:16 PM
-
By ch4r in forum Other Tutorials Forum
Replies: 5
Last Post: January 18th, 2005, 08:20 AM
-
By SonofGalen in forum The Security Tutorials Forum
Replies: 7
Last Post: February 9th, 2004, 11:14 AM
-
By CXGJarrod in forum Microsoft Security Discussions
Replies: 0
Last Post: July 23rd, 2003, 10:00 PM
-
By s0nIc in forum AntiVirus Discussions
Replies: 2
Last Post: January 27th, 2003, 12:23 PM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|