Help with query..

[snowyej]

I'm hoping someone can help me. I have very basic sql query knowledge and am having a hard time wrapping my head around this one.

I need to do two queries, one that will find a unique count of all the patients that were seen in a clinic I work for in the last two years (say from 5/1/2004 through 5/1/2006). The catch is that the patients who are under 18 have a guarantor attached to their account. This guarantor may also be the guarantor for another underage patient, so I only want the guarantor counted once.

The second query will probably be fairly easy once the first is figured out. I need to get addresses of all patients who have no guarantor attached to their record and were seen in the last two years and then addresses of all unique guarantors who are attached to patients that were seen in the last two years.

Does that make any sense at all?

So, I have three tables:

PatientVisit - has the "Visit" column that I can limit the dates on and "PatientProfileId"

PatientProfile - has "PatientProfileId" which matches with the column of same name in PatientVisit table, has the patient information columns (First, Last, Address1, Address2, etc..), has "PatientSameAsGuarantor" which has a 0 or <null> if the patient is their own guarantor (which usually means they're over 18), or a 1 which means the patient has a guarantor. If "PatientSameAsGuarantor" is a 1, "GuarantorId" is filled in with a guarantor Id.

Guarantor - has "GuarantorId" which matches to column of same name in the "PatientProfile" table. Also has guarantor name and address information.


I can get the count of patient visits in the last 2 years pretty easily:

Select Count(Distinct PatientProfileId) From PatientVisit Where Visit Between '5/1/2004' and '5/1/2006'


I'm having trouble matching to the other tables to get accurate counts. This is what I've come up with so far:

To get the count of unique guarantors attached to a patient:

Select Count(Distinct Guarantor.GuarantorId)
From PatientVisit
Inner Join PatientProfile On PatientVisit.PatientProfileId = PatientProfile.PatientProfileId
Inner Join Guarantor On PatientProfile.GuarantorId = Guarantor.GuarantorId
Where PatientProfile.PatientSameAsGuarantor = 1 And PatientVisit Between '5/1/2004' And '5/1/2006'


To get the count of unique patients without a guarantor:

Select Count(Distinct PatientProfile.PatientProfileId)
From PatientVisit
Inner Join PatientProfile On PatientVisit.PatientProfileId = PatientProfile.PatientProfileId
Where (PatientProfile.PatientSameAsGuarantor = 0 Or PatientProfile.PatientSameAsGuarantor is null) And PatientVisit Between '5/1/2004' And '5/1/2006'


Does this look right at all? I don't think I'm getting the correct numbers because the total of those (37342) is very close to the total for query just finding unique patients seen over last 2 years (37410) and I think there should be a much bigger difference.

Can anyone help?

I apologize if this is too long and scattered...my sql skills are kind of limited.

Thanks in advance!!

Elizabeth

 

[Mongr1l]

I don't think you have it right. Your inner joins are going to count patients more than once even though you're using a 'Distinct' keyword.

Try this:

Select count(*) from PatientProfile where (PatientSameAsGuarantor = 0 Or PatientSameAsGuarantor is null) and PatientProfileID In(Select PatientProfileID From PatientVisit where Visit Between '5/1/2004' and '5/1/2006')

Select count(*) from Guarantor where GuarantorID In(
Select GuarantorID from PatientProfile p, PatientVisit v where p.PatientProfileId = v.PatientProfileID and visit between '5/1/2004' And '5/1/2006')

Hope this helps

- mongril