January 7, 2009 at 10:11 am
DUH!!! All I need is a MAX/MIN on the application_id and it works. Thanks everyone! Here's my final query:
SELECT MIN(a.application_id) appplication_id,
d.brandname as drug,
dd.dosage,
c.short_name as clinic,
t.renew as renewal_date
FROM application a
JOIN tracking t ON a.application_id = t.application_id
JOIN drug d ON a.drug_id = d.drug_id
JOIN drug_dosage dd ON a.dosage_id = dd.dosage_id
JOIN clinic c ON a.clinic_id = c.clinic_id
JOIN (SELECT a1.drug_id,
a1.dosage_id,
a1.clinic_id,
MAX(t1.renew) as max_renew
FROM application a1
JOIN tracking t1 ON a1.application_id = t1.application_id
WHERE a1.patient_id = @patient_id
AND t1.renew IS NOT NULL
AND t1.renewed <> 1
AND t1.remove_renew <> 1
GROUP BY a1.drug_id, a1.dosage_id, a1.clinic_id) m
ON a.drug_id = m.drug_id
AND a.dosage_id = m.dosage_id
AND a.clinic_id = m.clinic_id
AND t.renew = m.max_renew
GROUP BY d.brandname, dd.dosage, c.short_name, t.renew
January 7, 2009 at 10:19 am
Nice work, Dave. This might do the job also...[font="Courier New"]SELECT a.application_id,
d.brandname,
dd.dosage,
t.renew,
c.short_name
FROM pap_application a
INNER JOIN tracking t ON a.application_id = t.application_id
INNER JOIN drug d ON a.drug_id = d.drug_id
INNER JOIN drug_dosage dd ON a.dosage_id = dd.dosage_id
INNER JOIN clinic c ON a.clinic_id = c.clinic_id
INNER JOIN (SELECT a.patient_id, d.brandname, dd.dosage, MAX(t.renew) AS RecentRenew, MIN(a.application_id) AS application_id
FROM pap_application a
INNER JOIN tracking t ON a.application_id = t.application_id
INNER JOIN drug d ON a.drug_id = d.drug_id
INNER JOIN drug_dosage dd ON a.dosage_id = dd.dosage_id
WHERE t.renew IS NOT NULL
AND t.renewed <> 1
AND t.remove_renew <> 1) r
ON r.patient_id = a.patient_id
AND r.application_id = a.application_id
AND r.brandname = d.brandname
AND r.dosage = dd.dosage
AND r.RecentRenew = t.renew
WHERE a.patient_id = @patient_id
AND t.renew IS NOT NULL
AND t.renewed <> 1
AND t.remove_renew <> 1[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 7, 2009 at 10:49 am
Glad we could help. You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply