Posted By

lonergan123 on 09/11/09

Tagged

Versions (?)

Last Edited at 09/11/09 12:45pm

Statistics

Viewed 75 times

Favorited by 0 user(s)

Related snippets

Allergy / Allergen Counts

/ Published in: SQL

Return counts for each drug / drug category allergen

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

SELECT Allergen1.Code, Allergen1.TypeCode, Allergen1.Description, Allergen1.ExternalDrugCode, Counts.AllergyCount
FROM CV3Allergen Allergen1
INNER JOIN
        (SELECT Allergen.GUID, COUNT(NULLIF(Allergy.AllergenGUID,0)) AllergyCount
        FROM CV3Allergen Allergen
        LEFT JOIN CV3AllergyDeclaration Allergy
        ON Allergy.AllergenGUID = Allergen.GUID
  AND Allergy.Status = 'Active'
  AND Allergen.TypeCode IN ('Drug', 'Drug Category')
        GROUP BY Allergen.GUID) Counts
ON Allergen1.GUID = Counts.GUID
ORDER BY 5 DESC, 1

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.