I would like to query all cases that had an action date GE to January 1, 2016 and to include any other records associated with that case regardless of the action date. As a result of the query, the dataset would include all of the records from CaseID 1234.
Re: ODBC - Query Cases Based on Date of Record (and Corresponding Records outside of date parameter)
It depends on what all your SQL supports (reading your question as you want to do this in ODBC query, not SPSS syntax directly). One way is to first make a table of the most recent date in one table, then merge it into the full table, then select based on the most recent date of a person. So some off-the-cuff SQL.
WITH Recent AS (
SELECT CaseId, MAX(ActionDate) AS RecentDate
LEFT JOIN Recent ON ?Table?.CaseId = Recent.CaseId
WHERE Recent.RecentDate >= '01-JAN-2016'
This could probably be made more efficient (maybe with an INNER JOIN). The idea would be for the recent table to only select those CaseId's meeting the cutoff. Then the second query only pulls out those individuals, instead of doing the match to the entire dataset. (Again it depends on what SQL is supported, such as if you can use IN to subset cases.)
Of course the same logic works in pure SPSS if that is what you wanted. Query the full table, use AGGREGATE to find the most recent dates for folks, and then select those out who have dates more recent than the desired cut-off.