aggregate functions and the HAVING clause

[arobbo]

Hi i read in a book that

"any query expressed using a HAVING clause can always be reqritten without the HAVING clause"

any clues as to how you could re write it

e.g. would you need to use a subquery etc ... my book doesn't elaborate.

basically i can't use the HAVING clause as the aggregate function only producees the data i want using its WHERE clause and i cannot use these fields in the Group by and (if my understanding is correct you cannot have a HAVING clause that has fields in it that aren't part of an aggregate function or group by)

i hope that makes sense

 

[PHV]

Why not elaborate on what you want to do ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[arobbo]

ok .....

basically i've got four tables

supplier_rebates_analysis_Co01 where i get invoice quantity and nett invoice price ... these two fields make up my aggregiate function which is sum(invoiceQuantity * nettInvoicePrice)

however

i only want this figure to be in a chosen rebate period time frame, so i link this table with my manufacturers table which then can be linked into my rebate table so that the WHERE clause can be between the start and finish dates of my rebate period. i then group this by my manufacturer code and my localised_rebate_ID

This gives me my yearToDateSpend for each manufacturer for each rebate period

However rebates are based on an incentive scheme so i have a sales_threshold table which gives me a different rebate percentage based on the yearToDateSpend, for example my salesthreshold table looks like this

ID /localised_rebate_ID/ sales_from / sales_to / rebate
1 1 0 10000 4
2 1 10001 15000 5
3 1 15001 20000 6

now that i have my yearToDateSpend i then want to display the rebate figure from the sales threshold table (above)

but in order to do this (my understanding ... which could well be wrong) is that i would need to use a HAVING clause to use the aggregate function calculating yearToDateSpend in determing the current rebate, however doing this would mean that all parts of my WHERE clause would need to either be aggregate functions on in my group by section which screws up the yearToDateSpend...

sorry if thats confusing is the best way i can explain it , would it help to see the SQL calculating the yearToDateSpend

thanks for your help, been struggling with this for a few days now

 

[arobbo]

Right this is the scenario again

I am trying to calculate what the rebate percentage from my suppliers would be for a rebate period.

To calculate this I need to have a linked table in my access database to (OUR SYSTEM) this gives me a table named Supplier Rebates Analysis Co01 , from this I use the suppliers Code to link this to my manufacturers table which can then be linked into my rebate table. The invoice date in my Supplier Rebates Analysis Co01 can then be referenced against the rebate date to and from giving me the nett invoice price and invoice quantity for the periods I want I can then multiply these fields together and get a sum to give me the yearToDateSpend. I group this by my man_Code and rebate_ID to then give me the yearToDateSpend for each manufacturer in each rebate period. The sql code for this is as follows

SELECT Sum([Supplier Rebates Analysis Co 01].[Invoice Quantity]*[Supplier Rebates Analysis Co 01].[Nett Invoice Price]) AS Expr1
FROM [Supplier Rebates Analysis Co 01] INNER JOIN (Manufacturer INNER JOIN Localised_Rebate ON Manufacturer.Man_ID = Localised_Rebate.man_ID) ON [Supplier Rebates Analysis Co 01].[Supplier Code] = Manufacturer.Man_Code
WHERE ((([Supplier Rebates Analysis Co 01].[Invoice Date]) Between [localised_rebate_Date_From] And [Localised_rebate_Date_To]))
GROUP BY Manufacturer.Man_Code, Localised_Rebate.Localised_Rebate_ID;

This works fine and is great …. But is only half of the story

In order to calculate the rebate percentage the yearToDateSpend then needs to be referenced against the sales_threshold table…. The sales_Threshold table is as follows


Sales_Threshold_ID Localised_Rebate_ID Sales_threshold_From Sales_Threshold_To Sales_Threshold_Rebate
1 2 £0.00 £15,000.00 4
2 2 £15,001.00 £20,000.00 5
3 2 £20,001.00 £99,999,999,999,999.00 6
4 3 £0.00 £99,999,999,999,999.00 0

I have written a basic query that returns the rebate percentage if I enter the yearToDateSpend, this is as follows

SELECT Sales_Threshold.Sales_Threshold_Rebate, localised_Rebate_ID
FROM Sales_Threshold where
yearToDateSpend BETWEEN sales_Threshold_From AND sales_Threshold_to
GROUP by localised_Rebate_ID, sales_THreshold_Rebate

I thought of using the first query that calculates the yearToDateSpend as a subquery in the second query however the first query returns more than one value and would want to know the rebate percentage for every value returned by the first query.

 

[alvechurchdata]

In general you can avoid a Having by splitting the process into two queries. The first query does the aggregate calculation and puts the result into a new field. The second query applies a Where clause to that new field.

Two smaller queries will sometimes run faster than a larger query and are much easier to debug.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PHV]

You wanted something like this ?
Create a saved query named, say, qryYearToDateSpend:
SELECT Sum(A.[Invoice Quantity]*A.[Nett Invoice Price]) AS yearToDateSpend, M.Man_Code, L.Localised_Rebate_ID
FROM [Supplier Rebates Analysis Co 01] AS A INNER JOIN (Manufacturer AS M INNER JOIN Localised_Rebate AS L ON M.Man_ID = L.man_ID) ON A.[Supplier Code] = M.Man_Code
WHERE A.[Invoice Date] Between [localised_rebate_Date_From] And [Localised_rebate_Date_To]
GROUP BY M.Man_Code, L.Localised_Rebate_ID;
And now your query:
SELECT Y.Man_Code, Y.Localised_Rebate_ID, T.Sales_Threshold_Rebate
FROM qryYearToDateSpend AS Y INNER JOIN Sales_Threshold AS T ON (Y.Localised_Rebate_ID = T.Localised_Rebate_ID) AND (
yearToDateSpend Between Sales_Threshold_From And Sales_Threshold_To);


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[arobbo]

genious .. that seems to work a treat...

i am truly greatful !!!!