Maybe subquery with IIF statement

[tmcrouse]

I have a table that I am trying to get to a Yes or No answer. The data looks like this:


memberID claimnbr serviceDT admitDT dischargeDT
1234 123456 1/1/2009 1/1/2009 1/5/2009
1234 654321 1/1/2009 1/1/2009 1/5/2009
1234 578900 1/15/2009 1/15/2009 1/31/2009
1909 578903 1/2/2011 1/2/2011 2/15/2011
1909 490312 4/11/2011 4/11/2011 5/16/2011
7899 393909 5/1/2011 5/1/2011 5/15/2011
7899 393909 5/1/2011 5/1/2011 5/16/2011


The records go on and on and the memberID is distinct however it appears multiple times due to varying claim numbers, service, admission and discharge dates.

What I want to do is say if the memberID appears more than one time look at the claim numbers. If the claim numbers are different then look at the dischargeDT. If the member has an admission date that is after that discharge date and is within 30 days then Yes. If not then No.

In this above example I would have 1 Yes for member 1234 because they were discharged from the initial admitDt on 1/5/2009 and then readmitted on 1/15/2009 which falls within that 30 day period. But then member 1909 would be a No because they did not have a readmission within the 30 day period.

[RayMilhon]

i USED 3 STEPS. STEP 1

SELECT MEMBID, CLAIMNO, SVCDATE, ADMDATE, DISCHDATE
FROM CLAIMS
WHERE SVCDATE BETWEEN [START] AND [END]

sAVE AS QRY_READMITS_STEP1

STEP 2
SELECT A.MEMBID AS MEMBID, A.SVCDATE AS FIRST_SVCDATE, A.ADMDATE AS FIRSTADMDATE, A.DISCHDATE AS, B.SVCDATE, B.ADMDATE, B.DISCHDATE
FROM QRY_READMITS_STEP1 A INNER JOIN CLAIMS B ON A.MEMBID = B.MEMBID AND B.ADMDATE > A.DISCHDATE

gIVING THE RESULTS
memID claim1 SVCDT1 ADMDT1 DISDT1 CLAIM2 SVCDT2 ADMDT2 DISDT2
1234 123456 1/1/09 1/1/09 1/5/09 578900 1/15/09 1/15/09 1/31/09
1909 578903 1/2/11 1/2/11 2/15/11 490312 4/11/11 4/11/11 5/16/11

sAVE AS QRY_READMITS_STEP2

STEP3
sELECT MEMBID, IIF(datediff("d",admdt2,disdt1)<31,True,False) FROM QRY_READMITS_STEP2.

[RayMilhon]

Field names have been changed to protect the innocent. Actually my typing is just really bad and I was too distracted to check it before submitting