SQL clause: HAVING

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 following has been added:

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

does it just exclude rows with Date_Diff <= 365?

What should be the effect of the HAVING clause?

EDIT: I don't experience what they say here. A copy of the mdb file is located at http://hotfile.com/dl/40641614/2353dfc/test.mdb.html (no macros or viruses). To execute queries, VISDATA.EXE is used.

EDIT2: I think the problem may be VISDATA because I experience different results through DAO.

Answer 1

, , . "HAVING" "WHERE", () (, MAX SUM, COUNT, ).

Answer 2

, .

Answer 3

, .

Answer 4

WHERE , WHERE MAX (...) .

HAVING WHERE, . , , HAVING count (*) > 1, .

, , , (MAX) 365. MAX (date), , date_diff <= 365.

MIN () 365. "rows" date_diff <= 365, max (date_diff) <= 365.

, ...

Answer 5

, - MAX. MAXing invoice.date , . , HAVING , 365 .

, ? , - ? , MAX .

Answer 6

, . having , , -.

-, -, where , :

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 7

GROUP BY. 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));

SQL92:

  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 customer numbers that had an invoice last year, and then execute OUTER JOIN in this result set to return only those that were not in the customer set with accounts last year.