Error message after querying this statement. Please help correct. Thanks!!

Question

SELECT PO.VENDOR_ID, V.NAME from VENDOR, MAX(PO.ORDER_DATE)

PURCHASE_ORDER LEFT OUTER JOIN PURCHASE_ORDER PO ON V.ID = PO.VENDOR_ID

WHERE EXISTS (SELECT V.ACTIVE_FLAG FROM VENDOR V WHERE V.ID = PO.VENDOR_ID and V.ACTIVE_FLAG = 'Y')

GROUP BY PO.VENDOR_ID

HAVING MAX(PO.ORDER_DATE) < '01-JAN-2020'

Error:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'MAX'.

Also, we actually want to extract the vendor not being active within 365 days. not the exact date as "01-JAN-2020". Please help correct too. Thanks!!

Answer 1

I assume VENDOR.ID is the primary key of the VENDOR table. If so, I believe you want

SELECT V.ID, V.NAME, MAX(PO.ORDER_DATE)
FROM VENDOR V LEFT JOIN PURCHASE_ORDER PO ON V.ID = PO.VENDOR_ID
WHERE V.ACTIVE_FLAG = 'Y'
GROUP BY V.ID, V.NAME
HAVING DATEDIFF(day, MAX(ISNULL(PO.ORDER_DATE, '19000101')), GETDATE()) > 365;

Tom

Answer 2

Hi @Molly ,

Welcome to the microsoft TSQL Q&A forum!

Please try:

SELECT PO.VENDOR_ID,V.NAME,MAX(PO.ORDER_DATE)  
from VENDOR V.  
LEFT OUTER JOIN PURCHASE_ORDER PO ON V.ID = PO.VENDOR_ID  
WHERE  V.ACTIVE_FLAG = 'Y')  
GROUP BY PO.VENDOR_ID, V.NAME   
HAVING DATEDIFF(dd,MIN(PO.ORDER_DATE),MAX(PO.ORDER_DATE))>365  

If this does not solve your problem,please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.