Select within a select problem

[paulosinuk]

Hi,

Here's my issue.

I have a table that contains current discounts on products by centre. Table structure

CentreNumber - branch/site/centre
ProductID - Product on discount
EffectiveDate - The date that discount becomes available
SMOffers - contains Tier 1 discounts
SDOffers - contains Tier 2 discounts
RMOffers - contains Tier 3 discounts

I want to be able to pull the average discount for all products within a centre for the maximum effective date. That should give me the average discounts at this point in time.

I'm trying to use the following select statement in SQL server 2000 query analyser but get a very naff error message.

SELECT CentreNumber, MAX(EffectiveDate) AS Effect

(SELECT avg(SMOffers) FROM PRICINGAPP_ProductOffers WHERE CentreNumber=C.CentreNumber and effectivedate >= C.Effect) as AvgTier1

FROM PRICINGAPP_ProductOffers as C
group BY CentreNumber, Effect


I get the error message:

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'as'.

Any help would be greatly appreciated.

Thanks

Paul

 

[SQLBill]

First off, I see a comma missing:

SELECT CentreNumber, MAX(EffectiveDate) AS Effect,
(SELECT avg(SMOffers) FROM PRICINGAPP_ProductOffers WHERE CentreNumber=C.CentreNumber and effectivedate >= C.Effect) as AvgTier1

FROM PRICINGAPP_ProductOffers as C
group BY CentreNumber, Effect

Try that (comma after Effect and before (SELECT...)and let us know if it worked.

-SQLBill

 

[paulosinuk]

Hi,

We're making progress but now getting the error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Effect'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Effect'.

Thanks

Paul

 

[vidru]

Try taking 'Effect' out of your GROUP BY clause.

-dave