2

I am new to SQL and need to write a complex query. Can you please help?

I have two tables. One is called PATIENTS and the other one is called CASES. PATIENTS has a "patient number" and a date entered. CASES has "patient number," "case no." and "date modified." The two tables are connected with the "patient number." There are multiple "case no." associated with one "patient number" since one patient can have multiple cases.

I need to get the following records. All the patients (from PATIENTS) that have all the "cases modified date" older than a certain date. So if the date is June 20th 1999. Then I need all the patients, who have had no cases modified after 06-20-1999

I will appreciate any help. Thank you.

Improve this question
4
  • 5
    That is a simple not complex query. PLease show us what you have so far. I'll give you a hint, use a join. Commented Oct 14, 2009 at 13:16
  • No JOIN, HLGEM, she doesn't want the cases, just the patients. Robin Day's solution below is correct. Commented Oct 14, 2009 at 13:21
  • Is this a 2 part question? Patients who have never had a case before this date (All dates older). Patients who have ONLY cases after this date (No dates older). Commented Oct 14, 2009 at 13:35
  • Sounds like a typical school assignment Commented Oct 14, 2009 at 13:43

3 Answers 3

Reset to default
11 
SELECT
    *
FROM
    Patients
WHERE
    PatientId NOT IN(
        SELECT
            PatientId
        FROM
            Cases
        WHERE
            DateModified >= '06-20-1999'
    )
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This will return patients with No cases
My "assumption" is that the query is to return Patients which have had no activity after the specified date. Therefore I think it would be correct to show Patients with no cases. I may be wrong in my assumption though. As we know... it is the mother of all...
You may be correct. As worded, "All the patients (from PATIENTS) that have all the "cases modified date" older than a certain date.", it is ambiguous as to whether no cases qualifies or not.
it wouldn't be that hard to add a UNION to the sub query to include in the patients without cases, thus excluding them.
1 
SELECT patient_no
FROM patients
WHERE patient_no NOT IN (
  SELECT patient_no
  FROM cases
  WHERE date_modified >= '1999-06-20'
)

Not sure about that date format though.

Improve this answer

2 Comments

This will return patients with no cases
"Then I need all the patients, who have had no cases modified after 06-20-1999" - this statement is true for patients with no cases.
0

If all you need is the patients, and ALL the cases have to be modified before the date, then

Select * From Patients p
Where Exists      -- eliminates those with no cases/or no cases before date
    (Select * From cases 
     Where PatientNo = p.PatientNo
        And ModifiedDate < [DateValue])
  And Not Exists --  to eliminate patients with cases modified after date.. 
    (Select * From cases 
     Where PatientNo = p.PatientNo
        And ModifiedDate >= [DateValue])

If you need case data as well, use a join:

Select * From Patients p 
   Join Cases c on c.PatietNo = p.PatientNo 
Where c.Modifed < DateValue

EDIT: to change the after to before from @Larry's comment below, thx!

Improve this answer

2 Comments

Charles, I think the question is asking for patient data and no case data. Additionally, the patients required are the ones for whom all cases were modified before, not after, the given date.
I think it is good to note to the user that this query eliminates any Patients without any Cases. Business rules in their database may prevent that situation anyway.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.