SQL: HAVING clause

Question

See the following SQL statement:

SELECT datediff("d", MAX(invoice.date), Now) As Date_Diff
      , MAX(invoice.date) AS max_invoice_date
      , customer.number AS customer_number
FROM invoice 
    INNER JOIN customer 
        ON invoice.customer_number = customer.number
GROUP BY customer.number 

If the the following was added:

HAVING datediff("d", MAX(invoice.date), Now) > 365

would this simply exclude rows with Date_Diff <= 365?

What should be the effect of the HAVING clause here?

EDIT: I am not experiencing what the answers here are saying. A copy of the mdb is at http://hotfile.com/dl/40641614/2353dfc/test.mdb.html (no macros or viruses). VISDATA.EXE is being used to execute the queries.

EDIT2: I think the problem might be VISDATA, because I am experiencing different results via DAO.

Answer 1

Yes, it would exclude those rows.

Answer 2

I wouldn't use a GROUP BY query at all. Using standard Jet SQL:

  SELECT Customer.Number
  FROM [SELECT DISTINCT Invoice.Customer_Number
     FROM Invoice
     WHERE (((Invoice.[Date])>Date()-365));]. AS Invoices 
  RIGHT JOIN Customer ON Invoices.Customer_Number = Customer.Number
  WHERE (((Invoices.Customer_Number) Is Null));

Using SQL92 compatibility mode:

  SELECT Customer.Number
  FROM (SELECT DISTINCT Invoice.Customer_Number
     FROM Invoice
     WHERE (((Invoice.[Date])>Date()-365));) AS Invoices 
  RIGHT JOIN Customer ON Invoices.Customer_Number = Customer.Number
  WHERE (((Invoices.Customer_Number) Is Null));

The key here is to get a set of the customer numbers who've had an invoice in the last year, and then doing an OUTER JOIN on that result set to return only those not in the set of customers with invoices in the last year.

Answer 3

You may be trying the wrong thing with your MAX. By MAXing the invoice.date column you are effectively looking for the most recent invoice associated with the customer. So effectively the HAVING condition is selecting all those customers who have not had any invoices within the last 365 days.

Is this what you are trying to do? Or are you actually trying to get all customers who have at least one invoice from more than a year ago? If that is the case, then you should put the MAX outside the datediff function.

Answer 4

Yes, that is what it would do.

Answer 5

That depends on whether you mean rows in the table or rows in the result. The having clause filters the result after grouping, so it would elliminate customers, not invoices.

If you want to filter out the new invoices rather than the customers with new invoices, you should use where instead so that you filter before grouping:

select
  datediff("d",
  max(invoice.date), Now) As Date_Diff,
  max(invoice.date) as max_invoice_date,
  customer.number
from
  invoice 
  inner join customer on invoice.customer_number = customer.number
where
  datediff("d", invoice.date, Now) > 365
group by
  customer.number

Answer 6

As already pointed out, yes, that is the effect. For completeness, 'HAVING' is like 'WHERE', but for the already aggregated (grouped) values (such as, MAX in this case, or SUM, or COUNT, or any of the other aggregate functions).