Max(date) having clause

[sarahzhou]

Hello,
I am querying the latest events of two companies in their latest events from the code below.

create table WORK.MEETING_EVENTS as select * from connection to MYCONNECTION
(select
COMPANY_A,
COMPANY_A_ID,
EVENT_NAME,
COMPANY_B,
COMPANY_B_ID,
EVENT_ID,
EVENT_DT
FROM &MEETING_EVENTS_YEARLY.
WHERE UPPER(EVENT_NAME) IN ('SCHOOL HOLIDAY','ANNIVERSARY','NEW YEAR EVE','CHRISTMAS')
GROUP BY COMPANY_A,COMPANY_B
HAVING EVENT_DT = MAX(EVENT_DT)
);

I already added the group by clause but I still get the error:

Error: CLI description error: [SAS][ODBC SQL Server Wire Protocol Driver][Microsoft SQL Server]Column 'MEETING_EVENTS_YEARLY.EVENT_DT'
Invalid in HAVING clause because it is not included in aggregate function or GROUP BY clause. :
[SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Unable to parse batch due to compilation error.

Kindly help.

thanks

[PGStats · Accepted solution]

I guess SQL Server doesn't do auto-remerge like SAS SQL does. You will just have to do the merge yourself. Something like:

create table WORK.MEETING_EVENTS as 
select * from connection to MYCONNECTION
(select
a.COMPANY_A,
a.COMPANY_A_ID,
a.EVENT_NAME,
a.COMPANY_B,
a.COMPANY_B_ID,
a.EVENT_ID,
a.EVENT_DT
FROM &MEETING_EVENTS_YEARLY. as a inner join
(	select 
		COMPANY_A, 
		COMPANY_B, 
		MAX(EVENT_DT) as select_dt
	from &MEETING_EVENTS_YEARLY.
	WHERE UPPER(EVENT_NAME) IN ('SCHOOL HOLIDAY','ANNIVERSARY','NEW YEAR EVE','CHRISTMAS')
	GROUP BY COMPANY_A, COMPANY_B  ) as b
on a.COMPANY_A=b.COMPANY_A and a.COMPANY_B=b.COMPANY_B and a.EVENT_DT=b.select_dt
);

[sarahzhou]

Awesome,thanks, it works!