SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Server Query

    Hello,

    Hopefully someone can help me with this query.

    Here are the tables:

    BasketItem:
    idBasket - PK for each order/basket
    idBasketItem - PK for this table/each item in a specific basket
    idProduct - PK for each product
    intPrice - Price charged
    intQuantity - quantity ordered
    (more unimportant fields)

    Products:
    idProduct - PK for each Product
    intPrice - Regular price for the product
    intSalesAllowed - A 1 stored here means "Yes Sales are allowed"
    idProductType - PK for the product type (i.e. posters, banners, etc.)
    (more unimportant fields)

    TypeDiscount:
    idProductType - PK for the product type (posters, banners, etc.)
    intMinQuantity - Minimum Qty ordered to qualify
    intSalesAmount - Amount discounted
    intSalesPercent - Percent discounted
    chrPromoCode - does it require a promotion code? (no in this case)
    (more unimportant fields)

    Ok, I need to take an order (idBasket) and find out if they qualify for a Type Discount (Order 25 posters and save 25%...). So I need to create a stored procedure that will take idBasket and find all the idBasketItems and add up the quantities of each product type in the basket and check that number against the Minimum Qty qaulifier (intMinQuantity) and return the sales amount and percent (intSalesAmount/intSalesPercent) if it qualifies.

    Here is what I have so far but this is my first real big SQL Query:

    Code:
    create procedure sp_QtyDiscountCheck
    
    @idBasket int
    
    AS
    
    Select TypeDiscount.intSalesAmount, TypeDiscount.intSalesPercent, TypeDiscount.idProductType
    From BasketItem, Products, TypeDiscount
    where BasketItem.idBasket = @idBasket and
    BasketItem.idProduct = Products.idProduct and
    Products.idProductType = TypeDiscount.idProductType and
    Products.intSalesAllowed = 1 and
    Sum(BasketItem.intQuantity) >= TypeDiscount.intMinQuantity
    Does anyone have the answer? or some direction?

    Should I bring all the BasketItems into an ASP page and then write code that can check the quantities?

    Thanks,

    Matt

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i don't mean to sound flippant, but what was the main question?

    in general, it is always better to do things like calculations and so on in sql, rather than in application code, so the answer to the last question is no
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The main question is what should a working SQL statement look like. The one I have here does not work.

    Error:
    An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


    So there is something wrong with putting a SUM in the WHERE. But I thought there was probably something else wrong as well.

    Thanks,

    Matt

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if this query returns 0 results, then @idBasket has no items that qualify for discount --
    Code:
    select D.idProductType
         , D.intMinQuantity
         , D.intSalesAmount
         , D.intSalesPercent
         , D.chrPromoCode
         , count(*)           as items
         , sum(B.intPrice)    as price_charged
         , sum(B.intQuantity) as qty_ordered
      from BasketItem as B
    inner
      join Products as P
        on B.idProduct = P.idProduct 
       and P.intSalesAllowed = 1 
    inner
      join TypeDiscount as D
        on P.idProductType = D.idProductType 
     where B.idBasket = @idBasket 
    group
        by D.idProductType
         , D.intMinQuantity
         , D.intSalesAmount
         , D.intSalesPercent
         , D.chrPromoCode
    having sum(B.intQuantity) >= D.intMinQuantity
    otherwise, this gives the data you need for each product type in the basket
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, I've been to ask for some time and then kept forgetting, but:

    why do you format your code with the comma leading the line?
    Code:
     group
     	by D.idProductType
     	 , D.intMinQuantity
     	 , D.intSalesAmount
     	 , D.intSalesPercent
     	 , D.chrPromoCode
    Is it perhaps so you can do the following easily?
    Code:
      group
      	by D.idProductType
      	 , D.intMinQuantity
      	 -- , D.intSalesAmount
      	 , D.intSalesPercent
      	 , D.chrPromoCode
    Because it looks, well, crazy

  6. #6
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    THANKS!!

    Matt

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that's one reason

    another is that it's easier to delete the last one

    with the comma at the back, you have to delete a line PLUS go to the end of the previous line to edit that

    i dunno about you, but i'm not moving my mouse when i text edit, and it's easier to just delete a line using shift-downarrow and the delete key and then you're done

    but the most compelling argument is that you can easily see where the actual columns are if they are expressions

    for example, and do this honestly withou looking ahead, how many columns does this query produce --
    Code:
    select id ,
           Description ,
           length(Description) as len ,
           length(replace(Description,'games','')) as lrep,
           ( length(Description) - 
           length(replace(Description,'games','')) ) / 
           length('games') as occ ,
           length(Description) + 1 -
           length(replace(Description,' ','')) as wds,
           (( length(Description) - 
           length(replace(Description,'games','')) ) / 
           length('games') ) /
           (length(Description) + 1 - 
           length(replace(Description,' ','')) ) as occwds,
           ( length(Description) - 
           length(replace(Description,'games','')) ) / 
           length(Description) as occlen  
      from keyword_relevance 
     where Description like '%games%'
    give up?

    okay, now how many --> Simple Keyword Relevance

    later this spring, joe celko has a new book coming out called SQL Programming Style which you may find interesting
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would say 7. I counted the AS statements and the first expression...

  9. #9
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, on your site your table has 8 fields, but I don't see an "ID" expression in your query

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    well, there are 8

    and the AS keywords weren't that easy to spot, were they

    now be honest, when you went to my page, wasn't it easier counting commas?

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh jeez I don't believe it!!!
    I made the classic "Off-by-one-error"!!!

    But honestly, no, I would have made that mistake if I'd been counting the commas too.

    And yes, nice formatting is, well, nice.

    The refactoring and test driven development people claim that time spent formatting code to make it readable is time wasted. It's better to refactor and add unit tests to make the meaning obvious.

    I'm not sure how valid that approach is to SQL, where we are often confronted with lots of tables and lots of fields in each, leading to necessarily complicated queries.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    it does not apply to sql

    add unit tests to make the meaning obvious?

    maybe on mars!

    (i wish i knew what movie that was from -- i think it was tom arnold to his kid in a schwarzenegger movie, but i don't think it was "true lies" ...)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •