Results 1 to 9 of 9

Thread: SQL Query

  1. #1
    Banned
    Join Date
    Aug 2001
    Location
    Yes
    Posts
    4,424

    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?

  2. #2
    AO's Filibustier Cheap Scotch Ron's Avatar
    Join Date
    Nov 2008
    Location
    Swamps of Jersey
    Posts
    378
    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.

  3. #3
    Banned
    Join Date
    Aug 2001
    Location
    Yes
    Posts
    4,424
    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).

  4. #4
    AO's Filibustier Cheap Scotch Ron's Avatar
    Join Date
    Nov 2008
    Location
    Swamps of Jersey
    Posts
    378
    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.

  5. #5
    AOs Resident Troll
    Join Date
    Nov 2003
    Posts
    3,152
    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

  6. #6
    AO's Filibustier Cheap Scotch Ron's Avatar
    Join Date
    Nov 2008
    Location
    Swamps of Jersey
    Posts
    378
    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.

  7. #7
    Banned
    Join Date
    Aug 2001
    Location
    Yes
    Posts
    4,424
    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

  8. #8
    THE Bastard Sys***** dinowuff's Avatar
    Join Date
    Jun 2003
    Location
    Third planet from the Sun
    Posts
    1,253
    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:5B8:41:56:C5:63:56:88:C0

  9. #9
    Banned
    Join Date
    Aug 2001
    Location
    Yes
    Posts
    4,424
    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

  1. SQL Tutorial – Basics
    By mikester2 in forum Other Tutorials Forum
    Replies: 5
    Last Post: January 31st, 2005, 01:16 PM
  2. SQL Tutorial
    By ch4r in forum Other Tutorials Forum
    Replies: 5
    Last Post: January 18th, 2005, 08:20 AM
  3. Foot Printing with Host
    By SonofGalen in forum The Security Tutorials Forum
    Replies: 7
    Last Post: February 9th, 2004, 11:14 AM
  4. Heads Up - Cumulative Patch for Microsoft SQL Server (815495)
    By CXGJarrod in forum Microsoft Security Discussions
    Replies: 0
    Last Post: July 23rd, 2003, 10:00 PM
  5. SQL Sapphire Worm Analysis
    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
  •