Below are some Queries that may be required for United Way.

Active Patient count by age and gender for patients with first visit date in the date range

/*United Way Report. Active Patient count by age and gender for patients with first visit date in the date range*/
SET @FromDate='2013-07-01', @ToDate='2013-09-30';
SELECT (CASE WHEN (YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5))<120
THEN (YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5)) ELSE 'NONE' END)
AS 'Age',
p.Gender,
COUNT(DISTINCT p.PatNum) AS 'Patients'
FROM patient p
WHERE p.PatStatus=0
AND p.DateFirstVisit BETWEEN @FromDate AND @ToDate
GROUP BY Age, Gender
ORDER BY Gender ASC,Birthdate DESC;

Active Patient count by race for patients with first visit date in the date range

/*United Way Report. Active Patient count by race for patients with first visit date in the date range. ONLY FOR OD VERSIONS BEFORE 13.2*/
SET @FromDate='2014-01-01', @ToDate='2014-02-01';
SELECT p.Race,
COUNT(DISTINCT p.PatNum) AS 'Patients'
FROM patient p
WHERE p.PatStatus=0
AND p.DateFirstVisit BETWEEN @FromDate AND @ToDate
GROUP BY p.Race
ORDER BY p.Race;

Active Patient count for patients with specific health problem with first visit date in the date range

/*United Way Report. Active Patient count for patients with specific health problem with first visit date in the date range. ONLY FOR OD VERSIONS BEFORE 13.2*/
SET @FromDate='2014-01-01', @ToDate='2014-02-01';
SET @ProblemName='%Cholera%';
SELECT (CASE WHEN d.DiseaseDefNum=0 THEN icd9.Description ELSE ddef.DiseaseName END) AS 'Problem',
COUNT(DISTINCT p.PatNum) AS 'Patients'
FROM patient p
INNER JOIN disease d ON d.PatNum=p.PatNum AND d.ProbStatus=0
LEFT JOIN diseasedef ddef ON ddef.DiseaseDefNum=d.DiseaseDefNum
LEFT JOIN icd9 ON icd9.ICD9Num=d.ICD9Num
WHERE p.PatStatus=0
AND p.DateFirstVisit BETWEEN @FromDate AND @ToDate
AND ((d.DiseaseDefNum=0 AND icd9.Description LIKE @ProblemName) OR (d.DiseaseDefNum!=0 AND ddef.DiseaseName LIKE @ProblemName))

Active Patient count for patients with specific patfield value and first visit date in the date range

/*United Way Report. Active Patient count for patients with specific patfield value and first visit date in the date range*/
SET @FromDate='2014-01-01', @ToDate='2014-02-01';
SET @FieldName='%Occupation%', @FieldValue='%Dentist%';
SELECT pf.FieldName AS 'PatField',
pf.FieldValue AS 'FieldValue',
COUNT(DISTINCT p.PatNum) AS 'Patients'
FROM patient p
INNER JOIN patfield pf ON p.PatNum=pf.PatNum AND pf.FieldName LIKE @FieldName AND pf.FieldValue LIKE @FieldValue
WHERE p.PatStatus=0
AND p.DateFirstVisit BETWEEN @FromDate AND @ToDate
GROUP BY pf.FieldName,pf.FieldValue
ORDER BY pf.FieldName,pf.FieldValue