Multiple date query. HELP!!!

[Guest]

I have a table with multiple duplicate entries, and need to somehow filter
out dates based on a specific code. Here's an example of one multiple record.

SSN CODE STARTDATE ENDDATE
123 A 01/01/2005 01/31/2005
123 B 03/01/2005 04/30/2005
123 C 04/15/2005 04/20/2005
123 B 04/24/2005 05/15/2005

What I need is this:

IF a SSN has a CODE of C, did that SSN have ANY other CODEs with a STARTDATE
within 5 days of the ENDDATE of the occurence of the C code.

In the case above, the key entry is the third one (IF a SSN has a CODE of
C), THEN did that same SSN have any other CODEs within 5 days (STARTDATE), of
the 'C' CODE ENDDATE.

My guess is that I'd first have to eliminate any non-qualifiers. In the
example above, the first two rows are insiginificant, because the occur
BEFORE the initial 'C' CODE. I am just interested in reoccurrences within 5
days AFTER a 'C' CODE occured.

Anyone who can figure this one out gets a gold star.

 

[Guest]

within 5 days of the ENDDATE of the occurence of the C code.

Do you mean five days either side? So in this example, where the C code has
an ENDDATE of 4/20/2005, you would expect to see only the last entry? What
if there was an entry with a STARTDATE of 4/17/2005?

 

[Guest]

Anyway, here's an example that should get you close to what you need. You
may need to fine-tune it but the logic should serve you. The example assumes
a table called "temp" contains the data as you listed it in your original
message. If all you want is to know if there were any entries (rather than
listing them) you could simply use the count function on the SSN. Good luck.

SELECT t1.SSN, t1.ENDDATE, t2.STARTDATE
FROM temp AS t1, temp AS t2
WHERE (t1.SSN=t2.SSN)
AND (t2.STARTDATE > (t1.ENDDATE - 5)
AND t2.STARTDATE < (t1.ENDDATE + 5))
AND t1.CODE = 'C'
AND t2.CODE <> 'C'

 

[tina]

try

SELECT A.SSN, A.Code, A.StartDate, A.EndDate
FROM MyTable AS A, MyTable AS B
WHERE B.Code = "c" And A.SSN = B.SSN And A.StartDate Between B.EndDate And
B.EndDate+5

the above will pull records where the SSNs match and StartDate is within 5
days after the "c" record's EndDate - regardless of the subsequent record's
Code value. if you want to also eliminate subsequent records where the Code
= "c", then try

SELECT A.SSN, A.Code, A.StartDate, A.EndDate
FROM MyTable AS A, MyTable AS B
WHERE B.Code = "c" And A.SSN = B.SSN And A.StartDate Between B.EndDate And
B.EndDate+5 And A.Code Not Like B.Code

hth


"Arthur King of All Britons" <Arthur King of All
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Guest]

Only the date AFTER the C code. Got your other post as well. I'm not an
expert user... Where wold I enter that code?

 

[tw]

SELECT test.ssn, test.code, test.startdate, step1.enddate
FROM test INNER JOIN step1 ON test.ssn = step1.ssn
WHERE test.startdate Between [step1]![enddate] And ([step1]![enddate]+5)

This will get you only the start dates after the end date but within 5 days
of it.



"Arthur King of All Britons" <Arthur King of All
(e-mail address removed)> wrote in message
news:[email protected]...

 

[tw]

go to queries and open a query by design then select to view sql and copy
the sql statement in there changing the table name to your table name
"Arthur King of All Britons"