/ 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