﻿-- MFU_Strat2
SELECT TOP %OUTPUT_CRITERIA% %NAME_FIELD%, '0+' AS age_group, sex, period, SUM(Members) AS mb, SUM(dayssupply) AS ds, SUM(dispensings) AS dp
FROM %SD_TABLE% AS sd
WHERE period in (%PERIODS%) AND  sex in ('F') and age_group in ('0-1','2-4','5-9','10-14','15-18','19-21','22-44','45-64','65-74','75+') AND 
		      ((SELECT COUNT(age_group_id) FROM enrollment WHERE age_group_id=sd.age_group_id and sex=sd.sex and year=sd.period and drugcov = 'Y') > 0)
GROUP BY %NAME_FIELD%,  sex, period
ORDER BY SUM(%METRIC_TYPE%) DESC

-----

UNION ALL

(
SELECT TOP %OUTPUT_CRITERIA% %NAME_FIELD%, '0+' AS age_group, sex, period, SUM(Members) AS mb, SUM(dayssupply) AS ds, SUM(dispensings) AS dp
FROM %SD_TABLE% AS sd
WHERE period in (%PERIODS%) AND  sex in ('M') and age_group in ('0-1','2-4','5-9','10-14','15-18','19-21','22-44','45-64','65-74','75+') AND 
		      ((SELECT COUNT(age_group_id) FROM enrollment WHERE age_group_id=sd.age_group_id and sex=sd.sex and year=sd.period and drugcov = 'Y') > 0)
GROUP BY %NAME_FIELD%, sex, period
ORDER BY SUM(%METRIC_TYPE%) DESC
)
