Quantcast

ODBC - Query Cases Based on Date of Record (and Corresponding Records outside of date parameter)

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ODBC - Query Cases Based on Date of Record (and Corresponding Records outside of date parameter)

DKUKEC
Dear Listers,

I have a question concerning using SPSS to query databases.  I was hoping that someone could suggest a way to complete the following query using the following variables.

CaseID RECORD ActionDate
1234 1 12-MAY-2004
1234 2 12-MAY-2015
1234 3 12-MAY-2015
1234 4 12-MAY-2016
4321 1 31-DEC-2014
4321 2 01-SEP-2015
4321 3 05-SEP-2015
4321 4 31-DEC-2015
 
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.

CaseID RECORD ActionDate
1234 1 12-MAY-2004
1234 2 12-MAY-2015
1234 3 12-MAY-2015
1234 4 12-MAY-2016

Any suggestions would be much appreciated.

Cheers,
Damir
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ODBC - Query Cases Based on Date of Record (and Corresponding Records outside of date parameter)

Andy W
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
  FROM ?Table?
  GROUPBY CaseId
)
SELECT *
FROM ?Table?
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.
Andy W
apwheele@gmail.com
http://andrewpwheeler.wordpress.com/
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ODBC - Query Cases Based on Date of Record (and Corresponding Records outside of date parameter)

DKUKEC
Thank you Andy, I will try your approach and if all else fails, I'll pull the data into SPSS and use the aggregate function to cutoff the cases. Cheers Damir
Loading...