I recently migrated a database from Sybase ASE to Microsoft SQL 2008r2. One category of queries that was broken in transit is:
SELECT person_id FROM leaveperiods WHERE group_id=146 GROUP BY person_id HAVING start_date = max(start_date)
According to Sybase, this is a valid query. But Microsoft SQL is stricter, so I get this error:
Column 'leaveperiods.start_date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
While looking at this query to try and correct it, I realized that it does not make sense to me. Isn't this exactly the same as the below?
SELECT DISTINCT person_id FROM leaveperiods WHERE group_id=146
Or maybe I should write like this?
SELECT DISTINCT person_id FROM leaveperiods WHERE group_id=146 AND start_date IS NOT NULL
There are several queries like this in one application and I need to fix them without breaking anything else. Please help.