SQL Server Query

[matt_12511]

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:

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

[r937]

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

[matt_12511]

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

[r937]

if this query returns 0 results, then @idBasket has no items that qualify for discount --

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

[asterix]

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?

 group
 	by D.idProductType
 	 , D.intMinQuantity
 	 , D.intSalesAmount
 	 , D.intSalesPercent
 	 , D.chrPromoCode

Is it perhaps so you can do the following easily?

  group
  	by D.idProductType
  	 , D.intMinQuantity
  	 -- , D.intSalesAmount
  	 , D.intSalesPercent
  	 , D.chrPromoCode

Because it looks, well, crazy

[matt_12511]

THANKS!!

Matt

[r937]

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 --

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

[asterix]

I would say 7. I counted the AS statements and the first expression...

[asterix]

Hey, on your site your table has 8 fields, but I don't see an "ID" expression in your query

[r937]

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?

[asterix]

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.

[r937]

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" ...)