Age, Gender, Primary Provider, and Number of Visits by Patient

We need some basic demographics, along with primary provider and number of non-zero visits during a specified date range.

This is a pretty basic query, but it does include a subquery to provide the number of visits.

SELECT 
  a.lastname+', '+a.firstname+' '+a.id AS "Patient Name/ID",
  COALESCE(b.provcode,'') AS "Provider",
  (SELECT COUNT(*) 
   FROM sos.journal jou JOIN sos.jcharges chg 
   WHERE jou.ptnum = a.ptnum 
     AND jou.trandate BETWEEN '2000-01-01' AND '2016-12-31'
     AND jou.amount > 0) AS "# of Visits",
  sos.AGEINYEARS(a.DOB,TODAY()) AS "Age",
  a.sex AS "Gender" 
FROM
  sos.patients a 
  LEFT OUTER JOIN sos.providers b ON a.providernum = b.providernum
  WHERE "# of Visits" > 0
ORDER BY 
  "Patient Name/ID"